Excel VBA: Enabling Macro Settings

The standard way of doing this is to force the user to enable macros by use of a splash screen.

• All the sheets in the workbook except for a spash screen are made very hidden (which can only be changed via VBA or the VBA editor)
• if macros are enabled:
  1) When the workbook opens, the code unhides all these very hidden sheets
  2) When the workbook closes then all these sheets are made very hidden again
• If macros are not enabled then the user can only see the Splash Screen saying "Please enable macros, close and then re-open this file"

Two links with full code for this technique are listed below

1. Brad Yundt covers this here at TekTips
2. Jonske at VBAeXpress

Here is an alternate method to check if VBA is enabled without having to hide/show sheets. The problem with hiding/unhiding sheets is that the worksheet could be saved in an "enabled" state, be closed without saving, and then be re-opened and not display the warning.

Create the following function in a Module (not a worksheet or workbook macro):

Public Function MacrosEnabled()
    MacrosEnabled = True
End Function

Now, in whatever cell you want to display a notice regarding whether macros are enabled or not, enter the following equation:

=IF(ISERROR(MacrosEnabled()&NOW()),"Macros are disabled","Macros are enabled")

If you just use ISERROR(MacrosEnabled()) the value is not recalculated every time the spreadsheet is opened. By concatenating NOW(), the current time is read every time the spreadsheet is opened, and so the value of the cell is recalculated. I tested this method extensively, and it seems to very reliably indicate whether macros are enabled or not.

You can then apply conditional formatting to do a number of things if macros are disabled:

• Highlight the check-cell in a bright color, and perhaps change the "macros are disabled" text to include instructions on how to enable macros.

• "Hide" cells whose values depend on macros being enabled by setting their text color and background color to be the same.

Here is a spreadsheet that demonstrates this method.