Hi all,

I want to be able to stop a workbook being opened if macro's ar
diasbled, so i created a workbook with this in the auto_open and the
get the workbook to close.....well this works fine.....opens the nex
workbook...BUT! it does not initiate the Auto_open on the workbook i
opens which kind of defeats the object.............

Any solutions welcome!

With Application
Workbooks.Open ("P:\Public\Shift 1 Activity\Shift1Holidays06.xls")

End With

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=46740

Simon,

As you've found, if macros are disabled all VBA is disabled so your code
won't run.

A sloution I have used for this previously goes something like this:

1. Create a sheet with a warning telling your users they must enable macros
for your application to work correctly.
2. WIth macros enabled, add some code into the close workbook event that
hides all the worksheets except your warning sheet.
3. Add some more code to the Open workbook event that displays all sheets,
and selects the sheet you most want them to use.

Now, if your users run the workbook with macros disabled they see the
warning sheet, if the macros are enabled they see nothing unusual.

You may want to add other protection and maybe disable some menu to stop
your users enabling sheets manually even if the macros are disabled.

HTH, post back if you're unsure of how to do this and I'll provide some
details.

Neil
www.nwarwick.co.uk

"Simon Lloyd" wrote:


Hi all,

I want to be able to stop a workbook being opened if macro's are
diasbled, so i created a workbook with this in the auto_open and then
get the workbook to close.....well this works fine.....opens the next
workbook...BUT! it does not initiate the Auto_open on the workbook it
opens which kind of defeats the object.............

Any solutions welcome!

With Application
Workbooks.Open ("P:\Public\Shift 1 Activity\Shift1Holidays06.xls")

End With

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=467404

Take a look at .runautomacros in VBA's help. (You have to run that other
auto_open sub yourself.)

If you had used workbook_open (in the shiftlholidays06.xls) workbook, those
would have fired automatically.

(you could disableevents to prevent the workbook_open from firing.)


ps. You may want to give that shiftlholidays06.xls a nice password so that the
users can't open it directly.

(File|SaveAs|Tools|General Options)





Simon Lloyd wrote:

Hi all,

I want to be able to stop a workbook being opened if macro's are
diasbled, so i created a workbook with this in the auto_open and then
get the workbook to close.....well this works fine.....opens the next
workbook...BUT! it does not initiate the Auto_open on the workbook it
opens which kind of defeats the object.............

Any solutions welcome!

With Application
Workbooks.Open ("P:\Public\Shift 1 Activity\Shift1Holidays06.xls")

End With

Simon

--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=467404

--

Dave Peterson