How to make a user enable Excel macros

Users can set thier Excel Security to High, Medium or Low.

Click on Tools-Macros-Security to set your Security level.

High Security All Excel macros (except from trusted sources)are disabled

Medium Security (recommended) User is prompted on each file-open to enable or disable Excel macros

Low Security (not recommended) Excel Macros can run automatically without a prompt. You are not protected from potentially unsafe macros.

Developer’s cannot control a user’s security settings and cannot force a user to run macros without seeing the pop-up security warning.

If you wish users to enable macros you can make the Excel file unusable unless they are enabled. All sheets (except one) can be hidden unless the user enables macros.

In the example below all sheets (except one) are hidden when the file is saved or closed.

When the file is opened with Excel macros disabled the user will only see 1 sheet with a warning message.
ie in cell b10: “Excel Macros must be enabled to use the workbook. Please close and reopen this file with Macros enabled”

When the Excel file is opened with macros enabled the hidden worksheets will be automatically unhidden and the warning sheet will be hidden.

When the Excel file is saved, all sheets except one are hidden.
If the user saves without closing then the sheets remain hidden until the cursor is moved on the visible sheet.

Note: Sheets are hidden on file-saving not file-closing.
I used this logic as a user may save a workbook without closing. This would leave a copy of the saved workbook with unhidden sheets on the drive which others could open in read-only mode.

Click here for an example file with macros: MakeUsersEnableMacros.xls

Copy this code to the ThisWorkBook macro sheet:

Private Const dsWarningSheet As String = "sheet1" 'Enter name of the Entry/Warning Page


	
Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean,  
Cancel as Boolean)
For Each ds In ActiveWorkbook.Sheets
        If LCase(dsWarningSheet) = LCase(ds.Name) Then
        ds.Visible = True
        Else
        ds.Visible = xlVeryHidden
        End If
    Next
End Sub

	
Private Sub Workbook_SheetSelectionChange(ByVal ds As Object, ByVal Target As Excel.Range)
If LCase(ds.Name) = LCase(dsWarningSheet) Then
      For Each ds In ActiveWorkbook.Sheets
            ds.Visible = True
            Next
      ActiveSheet.Visible = xlVeryHidden
      End If
End Sub

	
Private Sub workbook_open()
Sheets(dsWarningSheet).Select
For Each ds In ActiveWorkbook.Sheets
      ds.Visible = True
      Next
ActiveSheet.Visible = xlVeryHidden
End Sub

Home

Excel Consulting
Excel Security and Protection
Free Microsoft Excel Templates
Excel Calendars

Excel Corrupt File Recovery
Excel File Converters
Free Excel Help Resources
Excel Books
Excel Training

Excel Software
Business Templates
Stock Market
Financial Analysis

Scheduling

Project Management
Construction Calculators
Cost Estimating
Manufacturing

Excel BarCodes
Contact

Privacy Policy

Online Word and Excel password removal in under 3 minutes!