Excel/Macro Security


QUESTION: Simple question, I think. I have a file with Marcos in it. I am on on excel 2003. My security setting are set to Medium. If I double click on the file to open it, I always get a prompt to Disable or Enable the macros. This is what I want. If I create a simple macro that will open the above file, the prompt never comes up and the macros are disabled. I would like it to prompt me as it does when I physically open it. Is this a bug with excel 2003 or can you not open files with VBA that have macros in them.

Thanks in advance for your opinion on this.


Very odd. What *should* happen is if you open a file usig VBA, macro's are enabled by default, reasoning behind that being you already trusted some code to run.

You can control this using the AutomationSecurity property:

Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open "SomeBook.xlsm"
Application.AutomationSecurity = msoAutomationSecurityByUI

---------- FOLLOW-UP ----------

QUESTION: I don;t want to disable the macros from running. In this file they are set tu run on open and will not when I open the file using a VBA command.

ANSWER: I know. By default macros ARE enabled if you open a file programmatically.

I suspect there is something else at play here. Do you by any chance have an Auto_Open macro in that workbook, which you are expecting to be exectuted? If so, have a look at the runAutomacros command.

Otherwise, use the Workbook_Open event in the opened workbook instead of Auto_Open.

Finally, perhaps you are experiencing this issue:


---------- FOLLOW-UP ----------

QUESTION: Sorry for the incomplete picture. I do have a workbook_open event and an Auto_Open macro in the file. I added the runAutomacros command it it does fire now. The only remaining issue now is that it does not give me the option to disable, it just runs the code without a prompt.

If you use this:

   Application.AutomationSecurity = msoAutomationSecurityByUI

you will be prompted whether or not to enable macros.

Also, If you turn off events before letting the code open the workbook then workbook_open will not be run:

Application.EnableEvents = False
'Open workbook now
Application.EnableEvents = True

And if you omit RunAutomacros, then Auto_Open will not be fired either.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.