Opening macro security from VBA on startup

3
votes

I currently have a code that on startup checks if the option "Trust access to the VBA project object model" is enabled or not.

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

I've made some code that does this in most cases, but I've encountered one problem that I don't know how to get around.

The code is as follows:

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 unless the macro settings are as default "Disable all macros with notification", in which case I activate the macro and then get a run-time error "-2147467259 (80004005) Method 'ExecuteMso' of object '_CommandBars' failed"

This error will only occur on the first startup, as I do not have to activate the macros on consecutive startups, unless I move the file location.

I've tried pausing the macro for two seconds, but that did nothing for me, and neither did an error handler that tried to grab the error and then try executing the Application.CommandBars.ExecuteMso ("MacroSecurity") line again. It ended up with the same error.

The debugger tells me that the error is in the Application.CommandBars.ExecuteMso ("MacroSecurity") line, but that's probably not much of a surprise with that error message.

2
vbaexcel
Have you considered instead of pausing 2 seconds, setting an Application.OnTime event that fires in 2 seconds? - CLR
I've tried things like this in the past and it's not easy - it's been made hard for a reason, to prevent malicious code from running on the user's computer. While I'm sure it's possible to do this, I've never been able to find a viable solution myself. - dwirony
@dwirony see a few solutions below :) - David Zemens
Well, this is a first. All answers are useful and works, but the one I like the most is not an answer, but a comment :) Thank you all for the suggestions - Christian Emil Johansen
@DavidZemens When I needed it, I was working with Word VBA, and my case was a little different - I was trying to bring up the STARTUP folder to allow the user to drag and drop a file into it, but it seemed hard to accomplish. - dwirony

2 Answers

2
votes

Thinking outside the box here...
What if you put a big message on the sheet which tells the user to activate macros, and then have an auto start macro delete or hide that message. That would bring the message to those who need it, but not the others.

1
votes

Simple solution as proposed by @CLR in comments above, but didn't work when I initially tested it (user error!). All code goes in ThisWorkbook module:

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

Slightly more elaborate: Add an MSForms.CommandButton to the worksheet which will open the security settings pane after user clicks it. Have the MsgBox prompt the user to click the button and then change the security settings.

In Module1, the Button's click event handler:

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

In ThisWorkbook module:

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