How deny open workbook if press Disable Macros?

[ctbaker]

Is there a way to check whether someone has selected the "Enable Macros" option and make it so that your excel workbook won't open if macros have not been enabled?

Thanks,
Chad

 

[ETID]

You could lock down the entire workbook,...then set an on open macro to unlock it. that way if the user disables macros the workbook will open but not be usable.

 

[ribhead]

You could also set the Security to Low so the user won't even be prompted to enable or disable. Just a thought.

I may not be very smart but I'm sure wirey!!!!

 

[ctbaker]

ETID,

I had previously tried your solution but once the person saves the excel file then I am sunk because the re-enabling of the locking down of the workbook happens on close which is after the file is saved. So when you reopen the file it is no longer locked down.

Perhaps there is a way to make excel give you the option of Enabling Macros or Do Not Open File instead of Enable/Disbale/Do Not Open?

Thanks,
Chad

 

[ETID]

can you put the code to "lock down the workbook" in the "before save" event of the workbook?

 

[xlbo]

ETID is on the right track here. There is a fairly "standard" way of doing this that involves 1 extra sheet (lets call it "Warning&quot
On workbook_Close event, all sheets except "Warning" are set to be hidden:

For each sht in thisworkbook.sheets
if sht.name <> &quot;Warning&quot; then
sht.visible = xlveryhidden
else
sht.visible = true
next

In the workbook open event, the reverse procedure is used:

For each sht in thisworkbook.sheets
if sht.name <> &quot;Warning&quot; then
sht.visible = true
else
sht.visible = xlveryhidden
next

Then, on the &quot;Warning&quot; sheet, you have some text that says that you cannot use this workbook without enabling macros. This means that if they open the wb and disable macros, they won't be able to see anything except the warning sheet and because the rest of the sheets are &quot;xlveryhidden&quot;, they can only be made visible via code.....which the user has disabled. Bit of a fudge but works quite well

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[ctbaker]

Geoff,

While this looks like the solution I have found that unless you add a ActiveWorkbook.Save as the last line of the BeforeClose routine that the user can choose to not save changes before closing the workbook and thus when they open the workbook next time and disable macros they will find the workbook without the warning sheet being displayed.

Is there a better step than using ActiveWorkbook.Save and forcing a save every time the user opens the excel workbook?

Thanks,
Chad

 

[xlbo]

Sorry but I don't get that - if they don't save the changes, then the sheets get reset to their previous state (XP anyway) ie xlveryhidden

Even if they do, is saving such a chore ??

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244