Opening File from Link Doesn't Open macros

[helpexcel]

I have code that opens another workbook when you click on the button. This workbook has macro's. When you open it outside of this workbook, it asks you if you want to run the marcos, and the sheet works. When i'm opening it through the button, it doesn't ask about macros and the sheet doesn't work. Is there something i need to put in the code to make sure the macros are opened and run?

thanks!

 

[bobsan42]

What is the code that opens the workbook?

 

[helpexcel]

Sub Open ()
Workbooks.Open "\\AAA\BBB\ MACROWORKSHEET.xlsm"
End Sub

 

[bobsan42]

Are you absolutely macros are Disabled?
1. If some other code has previously disabled application events the Workbook_open Event will not fire - so the code will not run but macros may be enabled nonetheless
2. Your security settings are disabling the macros in workbooks which are not trusted or digitally signed from a trusted publisher or g... knows what .
To deal with this check the value of Application.AutomationSecurity:
msoAutomationSecurityLow = 1 - This would enable all macros in newly opened
filesmsoAutomationSecurityByUI = 2 - This is the default behavior where each time it would ask whether you want to enable or disable macros
msoAutomationSecurityForceDisable = 3 - This would disable all macros in newly opened files

so including this line before opening the workbook will probably help:

Sub Open ()
dim appset: appset = Application.AutomationSecurity
Application.AutomationSecurity = 1
Workbooks.Open "\\AAA\BBB\ MACROWORKSHEET.xlsm"
Application.AutomationSecurity = appset
appset=null
End Sub

But it is a good idea to return it to it's current value after this.

 

[helpexcel]

ADVERTISEMENT

assuming the "appset=null" is what returns it to current value?

 

[bobsan42]

Nope. The line just above it does.

dim appset - this declares a variable of type Variant
appset = Application.AutomationSecurity - this saves your current setting to the variable
...
Application.AutomationSecurity = appset - this sets it back.
appset=null - this clears the value from the memory

 

[helpexcel]

thanks!

 

[bobsan42]

You are welcome.
Any feedback will be appreciated.