Ensuring Macros Are Enabled

This page illustrates a technique to allow a workbook to function only if macros are enabled.

Introduction

In large and complex workbooks and Excel-based applications, the workbook may work properly only if the user has macros enabled. If macros are disabled, you may not want the user to do anything. For security reasons, there is no way to force the user to enable macros. (If there were such a method, it would defeat the entire purpose of allowing macros to be disabled.) However, you can use code to make your workbook operational only if macros are enabled.

This is accomplished by hiding every worksheet except one, which we call here the Introduction sheet. This introduction sheet does nothing and contains only a notice to the user that they must enable macros to use the workbook. The Workbook_Open event, which runs when the workbook is opened and macros are enabled, hides the introduction sheet and makes the other sheets visible (according to their normal Visible property), allowing full access to the workbook. If macros are not enabled, the Open event won't run and only the introduction sheet will be visible to the user.

Then, in the Workbook_BeforeClose event, all worksheet except the introduction sheet are made very hidden. Only the introduction sheet is visible. Thus, the next time the workbook is opened, if macros are disabled, only the introduction sheet is visible.

EXCEL SHEET VISIBILITY PROPERTIES: The visibility of a worksheet has one of three values: xlSheetVisible makes the sheet visible in the workbook;. xlSheetHidden, hides the sheet but allows the sheet to be made visible from the Sheet item on the Format menu; and xlSheetVeryHidden, which hides the sheet and does not allow the user to make the sheet visible. A sheet that is xlSheetVeryHidden can be made visible only through VBA code. The user cannot unhide very hidden sheets.

The code on this page will preserve the Visible property of each sheet. Thus, if you have sheets other than the introduction sheet that need to remain hidden or very hidden, the code will accommodate those sheets. It will not make visible any sheet that normally is hidden or very hidden.

The sheets' Visible property values are stored in a defined name so that the settings will be saved with the workbook. The Visible property of this defined name is False, so the user will not be able to change its value.

When deploying your workbook, the last saved version, the one that is distributed to the end users, should have:

• The Introduction sheet Hidden (xlSheetVeryHidden)
• All sheets that should be visible to the user when macros are enabled visible (xlSheetVisible)

It is assumed that the Application.EnableEvents property will be True when the workbook is opened and when it is closed. If EnableEvents is False, the workbook will behave as is macros are disabled, even if, in fact, macros are enabled.

Complete VBA Code

In the ThisWorkbook code module, paste the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the workbook's sheet visibility settings
    ' and hide all sheets except the introduction
    ' sheet. This Workbook_BeforeClose will run only
    ' if macros are enabled.
    ''''''''''''''''''''''''''''''''''''''''''''''''
    SaveStateAndHide
End Sub

Private Sub Workbook_Open()
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Unhide the sheets. This Workbook_Open event
    ' will run only if macros are enabled. If macros
    ' are not enabled, this code will not run and
    ' only the introduction sheet will be visible.
    ''''''''''''''''''''''''''''''''''''''''''''''''
    UnHideSheets
End Sub

Paste all of the following code into a regular code module. Change the lines marked with '<<<< CHANGE to the appropriate values.

C_SHEETSTATE_NAME is the name of a Defined Name in which the the Visible properties of all the worksheets are stored. There is no reason to change this value, but you can certainly do so.

C_INTRO_SHEETNAME is the name of the worksheet that should be displayed if the workbook is opened with macros disabled. This sheet should contain a text to the user indicating that the workbook should be opened with macros enabled along with instructions on how to enable macros.

C_WORKBOOK_PASSWORD is the password used to protect and unprotect the workbook. If your workbook has not protection, you can leave this value unchanged.

Option Explicit

Private Const C_SHEETSTATE_NAME = "SheetState"
Private Const C_INTRO_SHEETNAME = "Introduction"    '<<<< CHANGE
Private Const C_WORKBOOK_PASSWORD = "abc"           '<<<< CHANGE

Sub SaveStateAndHide()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SaveStateAndHide
' This is called from Workbook_BeforeClose.
' This procedure saves the Visible propreties of all worksheets
' in the workbook. This will run only if macros are enabled. It
' saves the Visible properties as a colon-delimited string, each
' element of which is the Visible property of a sheet. In the
' property string, C_INTRO_SHEETNAME is set to xlSheetVeryHidden
' so that if the workbook is opened with macros enabled, that
' sheet will not be visible. If macros are not enabled, only
' that sheet will be visible.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim S As String
    Dim WS As Object
    Dim N As Long
    ''''''''''''''''''''''''''''''''''''''''''''
    ' Protection settings. We must be
    ' able to unprotect the workbook in
    ' order to modify the sheet visibility
    ' properties. We will restore the
    ' protection at the end of this procedure.
    ''''''''''''''''''''''''''''''''''''''''''''
    Dim HasProtectWindows As Boolean
    Dim HasProtectStructure As Boolean
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the workbook's protection settings and
    ' attempt to unprotect the workbook.
    '''''''''''''''''''''''''''''''''''''''''''''''
    HasProtectWindows = ThisWorkbook.ProtectWindows
    HasProtectStructure = ThisWorkbook.ProtectStructure
    
    ThisWorkbook.Unprotect Password:=C_WORKBOOK_PASSWORD
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Make the introduction sheet visible
    '''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Worksheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Delete the Name. Ignore error if it doesn't
    ' exist.
    On Error Resume Next
    '''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Names(C_SHEETSTATE_NAME).Delete
    Err.Clear
    On Error GoTo 0
    For Each WS In ThisWorkbook.Sheets
        '''''''''''''''''''''''''''''''''''''''''''''''
        ' Create a string of the sheet visibility
        ' properties, separated by ':' characters.
        ' Do not put a ':' after the last sheet. Always
        ' set the visible property of the Introduction
        ' sheet to xlSheetVeryHidden. Don't put a ':'
        ' after the last sheet visible property.
        '''''''''''''''''''''''''''''''''''''''''''''''
        S = S & IIf(StrComp(WS.Name, C_INTRO_SHEETNAME, vbTextCompare) = 0, _
            CStr(xlSheetVeryHidden), CStr(WS.Visible)) & _
            IIf(WS.Index = ThisWorkbook.Sheets.Count, "", ":")
        '''''''''''''''''''''''''''''''''''''''''''''''
        ' If WS is the intro sheet, make it visible,
        ' otherwise make it VeryHidden. This sets all
        ' sheets except C_INTRO_SHEETNAME to very
        ' hidden.
        ''''''''''''''''''''''''''''''''''''''''''''''''
        If StrComp(WS.Name, C_INTRO_SHEETNAME, vbTextCompare) = 0 Then
            WS.Visible = xlSheetVisible
        Else
            WS.Visible = xlSheetVeryHidden
        End If
    Next WS
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the property string in a defined name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S, Visible:=False
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set the workbook protection back to what it was.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Protect C_WORKBOOK_PASSWORD, _
        structure:=HasProtectStructure, Windows:=HasProtectWindows
    
End Sub


Sub UnHideSheets()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' UnHideSheets
' This is called by Workbook_Open to hide the introduction sheet
' and set all the other worksheets to their visible state that
' was stored when the workbook was last closed. The introduction
' sheet is set to xlSheetVeryHidden. This maro is executed only
' is macros are enabled. If the workbook is opened without
' macros enabled, only the introduction sheet will be visible.
' If an error occurs, make the intro sheet visible and get out.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim S As String
    Dim N As Long
    Dim VisibleArr As Variant
    Dim HasProtectWindows As Boolean
    Dim HasProtectStructure As Boolean
    
    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Save the workbook's protection settings and
    ' attempt to unprotect the workbook.
    '''''''''''''''''''''''''''''''''''''''''''''''
    HasProtectWindows = ThisWorkbook.ProtectWindows
    HasProtectStructure = ThisWorkbook.ProtectStructure
    
    ThisWorkbook.Unprotect Password:=C_WORKBOOK_PASSWORD
    
    On Error GoTo ErrHandler:
    Err.Clear
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Get the defined name that contains the sheet visible
    ' properties and clean up the string.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo
    S = Mid(S, 4, Len(S) - 4)
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set VisibleArr to an array of the visible properties,
    ' one element per worksheet.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If InStr(1, S, ":", vbBinaryCompare) = 0 Then
        VisibleArr = Array(S)
    Else
        VisibleArr = Split(S, ":")
    End If
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the array and set the Visible propety
    ' for each sheet. If we're processing the C_INTRO_SHEETNAME
    ' sheet, make it Visible (since it may be the only
    ' visbile sheet). We'll hide it later after the
    ' loop.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For N = LBound(VisibleArr) To UBound(VisibleArr)
        If StrComp(ThisWorkbook.Sheets(N - LBound(VisibleArr) + 1).Name, C_INTRO_SHEETNAME) = 0 Then
            ThisWorkbook.Sheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
        Else
            ThisWorkbook.Sheets(N - LBound(VisibleArr) + 1).Visible = CLng(VisibleArr(N))
        End If
    Next N
    
    ''''''''''''''''''''''''''''''''
    ' Hide the INTRO sheet.
    ''''''''''''''''''''''''''''''''
    ThisWorkbook.Sheets(C_INTRO_SHEETNAME).Visible = xlSheetVeryHidden

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set the workbook protection back to what it was.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ThisWorkbook.Protect Password:=C_WORKBOOK_PASSWORD, _
        structure:=HasProtectStructure, Windows:=HasProtectWindows
    
    Exit Sub
    
ErrHandler:
    ThisWorkbook.Worksheets(C_INTRO_SHEETNAME).Visible = xlSheetVisible
    
End Sub

For an alternative technique, using formula calculations and defined names, to ensure that a workbook is opened with macros enabled, see the Ensuring Macros Are Enabled, Part 2 page.

This page last updated: 21-July-2007