• Force User To Enable Macros

    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!

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 5 Comments
    1. BKunkle's Avatar
      BKunkle -
      Although tricks like this allow you to lock down the workbook until macros are enabled, they can all be defeated if the user emails the workbook from within Excel once it has been opened and macros enabled. Typically, the recipient's computer will have macros disabled by default, so now the workbook is wide open and the macros are enabled.

      I have not been able to find a solution to that problem. There appears to be no event triggered by the sending of email and it is difficult to disable the send command in Excel 2007.

      However, I did work out a very nice reminder to enable macros that you can automate for users. It's quite simple:

      In the workbook open event, write the time and date in an otherwise unused cell. You might also set the foreground and background colors to be the same (so no one sees this time and date).
      ThisWorkbook.Worksheets(1).Range("M1") = Now

      Then, in any other unused cell, place this formula:
      =IF((NOW()-M1) > 0.01,"Enable Macros!!","")
      Format this cell as bold, red on white (or however you like). You can do is in multiple unused cells to more effectively warn your users.

      Change M1 to whatever cell you like. The 0.01 means one hundredth part of a day or 14.4 minutes.
    1. Ken Puls's Avatar
      Ken Puls -
      Interesting... I was not aware that the email code would bypass this functionality. I suppose you could probably disable the ability to send by overwriting keyboard shortcuts/disabling ribbon commands, but I haven't tried that myself.
    1. BKunkle's Avatar
      BKunkle -
      Quote Originally Posted by BKunkle View Post
      Although tricks like this allow you to lock down the workbook until macros are enabled, they can all be defeated if the user emails the workbook from within Excel once it has been opened and macros enabled. Typically, the recipient's computer will have macros disabled by default, so now the workbook is wide open and the macros are enabled.
      I should have written:
      ... so now the workbook is wide open and the macros are not enabled.
    1. Ken Puls's Avatar
      Ken Puls -
      I figured out what you meant.
    1. nite_rider's Avatar
      nite_rider -
      Thanks for the code! The version that i modified works great!
      The only problem that i faced was in the code below. When i tried to close the workbook, the save dialog box won't go away.
      Seems like the problem is here:

      If bSaved Then
      ThisWorkbook.Saved = True
      Cancel = True
      Else
      Cancel = True
      End If