Forcing Excel users to enable macros

Often if you create an Excel workbook with some automation features, you want to make sure that users enable macros to be sure the workbook behaves as designed. You can built in as many verification features as you want, but if the macros are disabled, they just won’t work.

If you search for solutions to this problem you”ll find many similar solutions; add a single sheet to your workbook to act as an instruction or warning to users that they must enable macros and then hide all other sheets in the workbook. Then using macos that, upon opening the workbook (or upon enabling macros) will hide the warning and unhide the regular sheets.

The moment to lock the workbook (show only the warning) is when saving it, so anyone opening it will be confronted with the warning, as designed. Then unlocking it should obviously happen when opening it or – and that is the tricky part – after it has been saved. Excel provides event handlers for ‘opening’ and ‘before saving’, but not for ‘after saving’. Fortunately there is a workaround for that.

[ad name=”468×60 Banner”]

To create an AfterSave event method, the Excel timer can be used. As VBA only runs on a single thread, the timer is not very accurate, as it only executes when Excel has time to execute it. But this ‘flaw’ can be used to our advantage; if in the ‘before save’ event we set a timer to expire now, then it will be executed at the earliest time Excel can on its single VBA thread. As Excel is at that moment busy running the BeforeSave event, then has to save the file, it will be only right after saving the file that it will be executed. And thats exactly what we need to unlock the workbook again.
One small caveat; the AfterSave created in this manner will not be executed when the ‘save’ is performed upon exiting Excel. But in this particular case that has no impact.

Here’s the basic code that goes into the ‘ThisWorkbook’ module;

[code lang=”vb”]
Option Explicit

‘=================================================================
‘ Code to force users to enable macros in Excel
‘ Created by: Thijs Schreijer
‘ http://www.thijsschreijer.nl
‘=================================================================

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘ this procedure runs when the workbook is saved

‘ Enable the macro warning before saving
Call LockWorkbook

‘ Set timer to unlock the workbook again after saving
‘ this runs the unlock macro as soon as Excel finds time,
‘ which is immediately after saving…, effectively creating
‘ an Workbook_AfterSave() command/event
Call Application.OnTime(Now(), “UnlockWorkbook”)

End Sub

Private Sub Workbook_Open()
‘ macro runs automatically when the workbook is opened

‘ Remove macro warning
Call UnlockWorkbook ‘ unlock upon opening

End Sub
[/code]

The code above, sets the Excel timer to run the ‘UnlockWorkbook’ code after the file has been saved. For the timer to work properly the code must be located in a public module. This module should then contain the following code;

[code lang=”vb”]
Option Explicit

‘=================================================================
‘ Code to force users to enable macros in Excel
‘ Created by: Thijs Schreijer
‘ http://www.thijsschreijer.nl
‘=================================================================

Sub UnlockWorkbook()
‘ Now here unlock the workbook, unhide the user sheets and
‘ hide the warnings sheet for example
End Sub

Sub LockWorkbook()
‘ Now here do whatever need to be done to lock the workbook,
‘ unhide the warnings sheet and hide all others
End Sub
[/code]

This method prevents you from having to override/mimic the default Excel behaviour of requesting you to save files if not saved already (including cancellation actions) as found in many other solutions.
A small drawback is the short flash of the warning when the user only saves the file, but doesn’t close it.

Here’s an example workbook (downloaded 945 times) to show you the results.

[ad name=”468×60 Banner”]

Be Sociable, Share!

4 thoughts on “Forcing Excel users to enable macros

  1. Great idea, looking forward to trying it out. Couldn’t you solve the “drawback” you list at the bottom – the short flash of the warning – by setting ScreenUpdating = false?

    • I think I tried that, but it didn’t work, probably due to the save action, which might display dialogs. Anyway, that trial was probably on Excel 2003. So please try and let me know how it works out!

  2. If you email the workbook from within Excel once it has been opened and macros enabled, does the code still work for the recipient of the mail?

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Subscribe without commenting