not allowing a file to open if macro's are disabled!?

[Simon Lloyd]

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

[Neil]

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
>
>

[Dave Peterson]

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