Enable/Disable Macros question

  • Thread starter Thread starter Paul Watkins
  • Start date Start date
P

Paul Watkins

Hi

I have a workbook that has a few macros in it which need to be enabled.
How can i close the workbook if someone clicks 'disable macros'.?

I would like to place the 'check' in the workbook open module if possible.


Thanks


Paul
 
Hi

I have a workbook that has a few macros in it which need to be enabled.
How can i close the workbook if someone clicks 'disable macros'.?

I would like to place the 'check' in the workbook open module if possible.
Click to expand...

I don't think that is possible.
I have seen a slightly different approach beeing used.
In the before close event hide all the sheets except one which just
says: "Please enable macros".
Then in the before open event unhide all sheets again. This will
obviously not prevent the more advanced users from opening your
workbook without macros enabled, but there is only so much one can do
to prevent people from making mistakes.

Hope this gave you some ideas.

Per Erik
 
Paul

If macros are disabled, the workbook_open would not run.

You can hide all the sheets except for a "Macros must be enabled to see
sheets" form or worksheet" when you close the workbook.

If users diasable macros all they get is the warning sheet message. If they
enable macros then the sheets are unhidden and good to go.

Gord Dibben Excel MVP
 
Stolen from another post:


Another alternative is to have the users open a dummy workbook that opens your
real workbook via code.

If the user has macros disabled, then the "dummy" macro won't run and the real
workbook won't be opened.

If the user enables macros, the dummy macro opens the real workbook and closes
itself.

In the dummy workbook, have a single worksheet that reads:
Close this workbook and reopen--but ENABLE MACROS!

The users might see a flash when things work the way you want.

The dummy macro could look something like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book1.xls"
ThisWorkbook.Close savechanges:=False
End Sub

When you're testing, save first. When it runs, it closes itself without saving!
 
Thanks


Paul
Dave Peterson said:
Stolen from another post:


Another alternative is to have the users open a dummy workbook that opens your
real workbook via code.

If the user has macros disabled, then the "dummy" macro won't run and the real
workbook won't be opened.

If the user enables macros, the dummy macro opens the real workbook and closes
itself.

In the dummy workbook, have a single worksheet that reads:
Close this workbook and reopen--but ENABLE MACROS!

The users might see a flash when things work the way you want.

The dummy macro could look something like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book1.xls"
ThisWorkbook.Close savechanges:=False
End Sub

When you're testing, save first. When it runs, it closes itself without saving!
Click to expand...
 
Back
Top