Additional Code that I have not tried...

 

Reference:  Ken Puls

​

Description:

​

Sometimes it is important to make sure that macros are enabled for your workbook. This is a technique to force the user to enable macros when they open your workbook. 

 

Discussion:

​

Since there is no way to use a macro to turn on macros, a technique to ensure the user has enabled macros is desirable. This particular method hides all sheets except a "welcome" sheet which tells the user to enable macros, and is enforced every time the workbook is saved. If the user opens the workbook with macros enabled, the sheets will all be unhidden by the macro. The hiding of sheets is also done using Excel VeryHidden property, which means that the sheets cannot be unhidden using Excel's menus. Keep in mind, however, that this only affects this workbook, so a user could use a macro from another workbook to unhide all of your sheets. Chances are, however, if your user is that skilled, they can always get into your file anyway.

 

NOTE:

 

To prevent some event looping issues, this code requires overruling Excel's built in Save events, and also requires replicating Excel's "Workbook has changed, do you want to save" prompts and actions. This code takes care of all of it. It does, however, create a very minor issue when closing the file. If the user tries to quit the application, it will close the workbook, but not Excel. Quitting again will close Excel completely. 

​

Code:

​

Option Explicit 

 

Const WelcomePage = "Macros" 

 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 

     'Turn off events to prevent unwanted loops

    Application.EnableEvents = False 

     

     'Evaluate if workbook is saved and emulate default propmts

    With ThisWorkbook 

        If Not .Saved Then 

            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ 

                vbYesNoCancel + vbExclamation) 

            Case Is = vbYes 

                 'Call customized save routine

                Call CustomSave 

            Case Is = vbNo 

                 'Do not save

            Case Is = vbCancel 

                 'Set up procedure to cancel close

                Cancel = True 

            End Select 

        End If 

         

         'If Cancel was clicked, turn events back on and cancel close,

         'otherwise close the workbook without saving further changes

        If Not Cancel = True Then 

            .Saved = True 

            Application.EnableEvents = True 

            .Close savechanges:=False 

        Else 

            Application.EnableEvents = True 

        End If 

    End With 

End Sub 

 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

     'Turn off events to prevent unwanted loops

    Application.EnableEvents = False 

     

     'Call customized save routine and set workbook's saved property to true

     '(To cancel regular saving)

    Call CustomSave(SaveAsUI) 

    Cancel = True 

     

     'Turn events back on an set saved property to true

    Application.EnableEvents = True 

    ThisWorkbook.Saved = True 

End Sub 

 

Private Sub Workbook_Open() 

     'Unhide all worksheets

    Application.ScreenUpdating = False 

    Call ShowAllSheets 

    Application.ScreenUpdating = True 

End Sub 

 

Private Sub CustomSave(Optional SaveAs As Boolean) 

    Dim ws As Worksheet, aWs As Worksheet, newFname As String 

     'Turn off screen flashing

    Application.ScreenUpdating = False 

     

     'Record active worksheet

    Set aWs = ActiveSheet 

     

     'Hide all sheets

    Call HideAllSheets 

     

     'Save workbook directly or prompt for saveas filename

    If SaveAs = True Then 

        newFname = Application.GetSaveAsFilename( _ 

        fileFilter:="Excel Files (*.xls), *.xls") 

        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname 

    Else 

        ThisWorkbook.Save 

    End If 

     

     'Restore file to where user was

    Call ShowAllSheets 

    aWs.Activate 

     

     'Restore screen updates

    Application.ScreenUpdating = True 

End Sub 

 

Private Sub HideAllSheets() 

     '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() 

     '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 

​

How to use:

 

1. Rename a worksheet in your workbook to "Macros".

2. Put a message on the page telling the user to enable macros.

3. Copy above code.

4. In Excel press Alt + F11 to enter the VBE.

5. Press Ctrl + R to show the Project Explorer.

6. In the project explorer, locate the ThisWorkbook object.

7. Double click the ThisWorkbook object.

8. Paste code into the right pane.

9. Press Alt + Q to close the VBE.

10. Save workbook before any other changes.

11. Close and reopen the workbook.

 

 

Test the code:

 

1. If you reopen the workbook with macros disabled, you should just see your warning screen.

2. If you reopen the workbook with macros enabled, you should see your warning screen for a second, then you should see all of your other sheets and the warning screen will dissappear.