Making sure the user is able to run macros

[GPO]

I have an Excel application that contains macros. For it to function as
intended, the user's security settings need to allow macros to run. I'm
wondering how best to advise the user that they have not got macros enabled.
Is the following a legitimate approach?

On file open, the only visible sheet simply has a message on it saying
something like
"It appears that you have not enabled macros to run. Please enable macros in
order to use this workbook."

If the workbook_open() event can successfully fire, it will set the .visible
property from xlSheetVisible to xlSheetVeryHidden (I wanted
xlSuperDuperHidden but they don't have that yet) and set all the sheets I
want users to see from xlSheetVeryHidden to xlSheetVisible.

On Workbook_BeforeClose() reverse the process.

Are there any gotchas with this approach? It could also be a workaround for
people opening the file within Internet Explorer instead of saving it locally
and then opening it with Excel. Any advice gratefully received.

Cheers

GPO

 

[Doug Glancy]

GPO,

The gotcha has to do with how to handle saves. Basically you need to
capture every BeforeSave event and do your reversal there, so that each save
also inlcudes the reversal. Otherwise they could make some changes, save
the sheet, but not save at closing:

1 note which sheets are currently hidden
2 hide all the sheets except the notification sheet
3 do a save in the code
4 unhide the sheets that you just hid so the workbook looks the same
5 cancel the BeforeSave

hth,

Doug