• Force User To Enable Macros

    by
    Ken Puls
    Published on 2007-11-11 04:57 AM      Number of Views: 10665  
    1 Comment Comments
    Categories:
    1. Excel - VBA Programming
    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: 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
    Versions Tested:
    • This function has been tested with Excel 2007, and should also work with Excel 97 through 2003 without any modifications.
    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 (*.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!

     

    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 1 Comment
    1. BKunkle's Avatar
      BKunkle - 2013-02-18, 03:28 PM
      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.
Gravatar by 1e2.it