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:

1. Notify the user that they need to enable macros to work with your file.

Examples of where this function shines:

1. Advises users to re-open the workbook with macros enabled
2. Sets all other worksheets to "VeryHidden" which means that they cannot be unhidden except through the visual basic editor

Macro Weakness(es):

1. A user versed in VBA will still be able to unhide your sheets through the VBE

Versions Tested:

1. This function has been tested with Excel 2007, and should also work with Excel 97 through 2003 without any modifications.

Instructions for use:

1. 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:

1. 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

Special Thanks:
A special thank you to Phil Jollans and Tony Jollans for inspiring me to revisit this code and update it!