CODEWITHSUNDEEP
excelexcel-2010excel-2013vba
David F
David F

Reputation: 71

Force Excel user to enable macros

Is there a way to prevent an Excel workbook from opening if the user has not Enabled Macros

Upvotes: 1

Views: 10331

Answers (3)

GMc
GMc

Reputation: 1774

You can protect the spreadsheet with a password.

This can be found in Review / Protect Workbook.

When the user tries to open the spreadsheet they will be prompted for the password. If they do not know it, they can not open it.

This approach does not require VBA (nor macros).

Upvotes: 0

user861380
user861380

Reputation: 1

Using the below vb script is forcing the macros to be auto enabled for me. Hope it helps others

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = True

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.SendKeys "%{F11}"
WshShell.SendKeys "% n"

Set objWorkbook = objExcel.Workbooks.Open("C:\Macro File.xlsm")

Upvotes: 0

brettdj
brettdj

Reputation: 55672

The standard way of doing this is to force the user to enable macros by use of a splash screen.

It is also possible to modify the VBA settings by writing to the registry (for example using a VBS- although in a corporate setting a GPO may prevent this). See http://blogs.msdn.com/b/cristib/archive/2012/02/29/vba-programmatically-enable-access-to-the-vba-object-model-using-macros.aspx for an example of accessing the registry.

Splash screen approach

  • All the sheets in the workbook except for a splash screen are made very hidden (which can only be changed via VBA or the VBA editor)
  • if macros are enabled:
    1) When the workbook opens, the code unhides all these very hidden sheets
    2) When the workbook closes then all these sheets are made very hidden again
  • If macros are not enabled then the user can only see the Splash Screen saying "Please enable macros, close and then re-open this file"

Two links with full code for this technique are listed below

  1. Brad Yundt from here at TekTips

code goes in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ThisWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Dim Pswd As String
Pswd="myPassword"
Application.ScreenUpdating = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then 
       If ws.Name="Sheet1" Then
           If InputBox("Please enter your password")=Pswd Then ws.Visible=xlSheetVisible
       Else
           ws.Visible = xlSheetVisible
       End If
   End If
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
  1. Jonske at VBAeXpress

code goes in the ThisWorkbook module

Option Explicit 

Private Sub Workbook_Open() 

    With Application 
         'disable the ESC key
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 

        Call UnhideSheets 

        .ScreenUpdating = True 
         're-enable ESC key
        .EnableCancelKey = xlInterrupt 
    End With 

End Sub 
 '
Private Sub UnhideSheets() 
     '
    Dim Sheet As Object 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Name = "Prompt" Then 
            Sheet.Visible = xlSheetVisible 
        End If 
    Next 
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing 
    ActiveWorkbook.Saved = True 

End Sub 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 

        Call HideSheets 

        .ScreenUpdating = True 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 

Private Sub HideSheets() 
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt") 
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved" 
         '
        .Visible = xlSheetVisible 
         '
        For Each Sheet In Sheets 
            If Not Sheet.Name = "Prompt" Then 
                Sheet.Visible = xlSheetVeryHidden 
            End If 
        Next 
         '
        If .[A100] = "Saved" Then 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        End If 
         '
        Set Sheet = Nothing 
    End With 
     '
End Sub

Upvotes: 4

Related Questions