Introduction:
There are times when you may need to force your users to enable macros to work with your files. Maybe you've programmed some special functions to work with your file, for example. Unfortunately, Office's security settings can get in the way if you really need your macros enabled, as your users could have their system set up to disable macros by default, or they could choose to disable them for some reason. The macro solution below gives you a way to deal with this issue.
Macro Purpose:
- Notify the user that they need to enable macros to work with your file.
Examples of where this function shines:
- Advises users to re-open the workbook with macros enabled
- Sets all other worksheets to "VeryHidden" which means that they cannot be unhidden except through the visual basic editor
Macro Weakness(es):
- PLEASE NOTE: This article was adapted 2013-06-12 with modifications that should resolve issues identified here.
- A user versed in VBA will still be able to unhide your sheets through the VBE
Versions Tested:
- This function has been tested with Excel 2013, and should also work with any version of Excel that supports the xlsm workbook file format without any modifications (Excel 2007 and higher, plus 2000-2003 where the Office Compatibility Pack is installed)
Instructions for use:
- Rename one of your worksheets "Macros" and place instructions to your users, indicating that they will need to re-open the file with macros enabled.
VBA Code Required:
- Place the following code in the ThisWorkbook module of your workbook:
Code:
Option Explicit
Const WelcomePage = "Macros"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Ensure that the macro instruction sheet is saved as the only
' visible worksheet in the workbook
Dim ws As Worksheet
Dim wsActive As Worksheet
Dim vFilename As Variant
Dim bSaved As Boolean
'Turn off screen flashing
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Record active worksheet
Set wsActive = ActiveSheet
'Save workbook directly or prompt for saveas filename
If SaveAsUI = True Then
vFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlsm), *.xlsm")
If CStr(vFilename) = "False" Then
bSaved = False
Else
'Save the workbook using the supplied filename
Call HideAllSheets
On Error Resume Next
ThisWorkbook.SaveAs vFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Select Case Err.Number
Case Is = 1004
'User opted not to overwrite
Case Else
MsgBox "Unknown error, file not saved."
bSaved = False
GoTo ExitPoint
End Select
On Error GoTo 0
'Add file to most recent files list
Application.RecentFiles.Add vFilename
Call ShowAllSheets
bSaved = True
End If
Else
'Save the workbook
Call HideAllSheets
ThisWorkbook.Save
Call ShowAllSheets
bSaved = True
End If
ExitPoint:
'Restore file to where user was
wsActive.Activate
'Restore screen updates
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
'Set application states appropriately
If bSaved Then
ThisWorkbook.Saved = True
Cancel = True
Else
Cancel = True
End If
End Sub
Private Sub Workbook_Open()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Unhide all worksheets since macros are enabled
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub
Private Sub HideAllSheets()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub
Special Thanks:
A special thank you to Phil Jollans and Tony Jollans for inspiring me to revisit this code and update it!
