How to Check Macro Security Setting?

[Niranjan]

Hi
I have to distribute an Excel File containing macros to various users.
How can i make the file/macro check macro security setting of the users machine and advise him to enable macros otherwise the macros in the distributed excel file will not work.
Can anyone please help.
Niranjan

[paelinck]

You can't. If the user has blocked macros, you can't have a macro run automatically to check settings (the whole point of blocking macros is not having macros doing things automatically). I would therefore advise the following.

When closing the workbook, have the auto_close macro select a separate sheet before saving, which contains the warning you wish to give new users. By selecting the sheet before saving, you make sure the sheet is active when a user opens the workbook.
If the user has macros enabled, you can have the "Auto_open" macro select another sheet immediately, so the warning disappears.

I hope this will help.

Marc

[paelinck]

Here's a small improvement. Use the Workbook_BeforeSave event instead of Auto_Close to do the trick. This allows the user to close the workbook without saving. The Workbook_BeforeSave event should be placed in the code of the workbook object, which you can access by right-clicking on the Excel symbol left of the "File" menu option, and selecting "View Code".

Place the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
   Worksheets("Warning").Activate
End Sub

Where "Warning" should be replaced with the corresponding sheet name in your workbook.

Marc

[Niranjan]

Thanks Marc

[ This Message was edited by: Niranjan on 2002-04-23 00:23 ]

[Tom Schreiner]

Hi
If your code has part in the integrity of your data, formats, ect.
You may want to hide all of your sheets except the one showing the message.
If macros is not enabled then your workbook will be more protected.

Tom