CODEWITHSUNDEEP
excelvba
Cube707
Cube707

Reputation: 204

VBA code dies when opening a Excel Workbook with macros

When runnign the following code, excel promts me about the document not beeing from a trusted source and if macros should be disabled. That is all fine and how it should be. It works fine when clicking on enable macros BUT if I click on disable macros the code just dies silently and never reaches "Made it". The same happens when i use GetObject() which was in the original code.

Is there a way to open the worksheet and disable the macros by default, not promting the user? Or at least to somehow get the code to run again? I don't need them to run and it would be fine to just open the workbook read-only.

I know I can configure the Trust Center on my machine, but this ist for a company wide project, so thats not an option.

Also signing the code could maybe work?

Sub test()
    Dim FileName As String
    Dim test As Workbook

    FileName = ".\hello.xlsm"
    Set test = Workbooks.Open(FileName, , True)

    MsgBox "Made it"
End Sub

Upvotes: 0

Views: 268

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Try this:

Sub test()
    Dim FileName As String
    Dim test As Workbook

    FileName = ".\hello.xlsm"

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Set test = Workbooks.Open(FileName, , True)
    Application.AutomationSecurity = msoAutomationSecurityByUI

    MsgBox "Made it"
End Sub

Upvotes: 1

Related Questions