Opening File from Link Doesn't Open macros

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
643
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
What is the code that opens the workbook?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
files
msoAutomationSecurityByUI = 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:
Code:
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.
 
Last edited:

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
643

ADVERTISEMENT

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
You are welcome.
Any feedback will be appreciated.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top