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.
- Notify the user that they need to enable macros to work with your file.
- 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
- PLEASE NOTE: Some issues have been discovered with this article. A new solution has been posted here. I have not tested this myself, but the feedbeck would indicated that it works where the code below does not.
- A user versed in VBA will still be able to unhide your sheets through the VBE
- This function has been tested with Excel 2007, and should also work with Excel 97 through 2003 without any modifications.
- 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.
- Place the following code in the ThisWorkbook module of your workbook:
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 (*.xls*), *.xls*") If CStr(vFilename) = "False" Then bSaved = False Else 'Save the workbook using the supplied filename Call HideAllSheets ThisWorkbook.SaveAs vFilename Application.RecentFiles.Add vFilename Call ShowAllSheets bSaved = True End If Else 'Save the workbook Call HideAllSheets ThisWorkbook.Save Call ShowAllSheets bSaved = True End If '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
A special thank you to Phil Jollans and Tony Jollans for inspiring me to revisit this code and update it!