Clever Geek Handbook

Opening VBA Macro Protection on Startup

I currently have some code that, on startup, checks if the Trust Access to VBA Project Object Model is enabled or not.

In case it is not enabled, I need a program to open the security options of the macro for easy access for the user.

I made some code that does this most of the time, but I ran into one problem that I don't know how to get around.

The code looks like this:

Private Sub Workbook_Open
If Not VBATrusted() Then
    MsgBox "Trust access to the VBA project object model is not enabled" & vbNewLine & vbNewLine & _
    "Please allow access by ticking the checkbox in the window that appears after clicking Ok"
    Application.CommandBars.ExecuteMso ("MacroSecurity")
End If
End Sub

Function VBATrusted() As Boolean
    On Error Resume Next
    VBATrusted = (Application.VBE.VBProjects.Count) > 0
End Function

This code does its job if the macro options are not set to the default "Disable all macros with notification", in which case I activate the macro and then get a runtime error "-2147467259 (80004005) ExecuteMso method of the _CommandBars error object"

This error only occurs on first launch, since I don't need to activate macros on successive launches unless I have moved the file location.

I tried to pause the macro for two seconds, but that didn't do anything for me, and the error handler trying to catch the error didn't try to execute the line Application.CommandBars.ExecuteMso ("MacroSecurity")

. This resulted in the same error.

The debugger tells me that the error is on line Application.CommandBars.ExecuteMso ("MacroSecurity")

, but that probably isn't all that surprising with the error message.

+3


source to share


2 answers


Simple solution as suggested by @CLR in the comments above, but didn't work when I tested it first (user error!). All the code is in the module ThisWorkbook

:

Option Explicit

Private Sub Workbook_Open()
If Not VBATrusted() Then
    MsgBox "Trust access to the VBA project object model is not enabled. " & vbNewLine & _
    "Please allow access by ticking the checkbox in the window that appears"
    Application.OnTime Now + TimeValue("00:00:01"), "ThisWorkbook.SetSecurity"
End If
End Sub

Function VBATrusted() As Boolean
    On Error Resume Next
    VBATrusted = (Application.VBE.VBProjects.Count) > 0
End Function

Sub SetSecurity(Optional foo)
    Application.CommandBars.ExecuteMso "MacroSecurity"
End Sub

In a little more detail: Add MSForms.CommandButton

to a worksheet which will open the security options panel after the user clicks on it. Ask the MsgBox

user to prompt the user to click a button and then change the security settings.

To Module1

the button click event handler:



Option Explicit
Sub Button1_Click()
    Call ThisWorkbook.SetSecurity
End Sub

In the module ThisWorkbook

:

Option Explicit

Private Sub Workbook_Open()
If Not VBATrusted() Then
    MsgBox "Trust access to the VBA project object model is not enabled. " & vbNewLine & _
    "Please allow access by:" & vbNewLine & vbNewLine & _
    "1. Clicking the button on this sheet" & vbNewLine & _
    "2. Ticking the checkbox in the window that appears"
End If
End Sub

Function VBATrusted() As Boolean
    On Error Resume Next
    VBATrusted = (Application.VBE.VBProjects.Count) > 0
End Function

Sub SetSecurity(Optional foo)
    Application.CommandBars.ExecuteMso "MacroSecurity"
End Sub
+1


source


Thinking outside the box here ...
What if you put a large message on the sheet that tells the user to activate the macros and then automatically run the macro or hide that message. It will bring a message to those who need it, but not others.



+2


source






More articles:


All Articles