Subscribe in a readerPreventing auto_open and Workbook_Open events from running
Introduction
As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running. This little article shows you how to achieve that.
Why would you want to do this? Typically, I use this on my own files in which I am still developing. The Open event might contain code that takes a while to run, or configures the project in a way which I don't want to happen when I start working with the file.
If the file you opened using the methods described below contains other event handlers, note that these event handlers will remain disabled until you start a macro manually (by clicking a command button or a menu entry) or if a User Defined function in your code has been called.
I do not recommend using this method to open files from sources you do not know or trust. The VBA code can contain events which will eventually run and possibly cause trouble.
Excel 2010
Macro security set to low or trusted document
If you have set your macro security to "Enable All Macros" or you have already set the document to trusted or the document is in a trusted folder, click File, Open, select your file and hold down the shift key when you click the Open button:
Fig. 1: Selecting the file from the File, Open dialog (Excel 2010)
Of course your file might be listed in the Most Recently Used files (MRU) list. In that case, hold shift when you click the file in the list:
Fig. 2: Clicking the file on the MRU (Excel 2010)
Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.
Macro security set to prompt
If your document is not in a trusted folder, has never been set to be trusted and macro security is set to something other than enable all macros to run, you can repeat what I've shown above.
Normally, if you open such a document, Excel will show the security bar. In this situation however, due to you holding down shift, Excel shows this window:
fig 3: Enable macros dialog (Excel 2010)
Because you held down the shift button when you clicked the file in the MRU or when you clicked the Open button, you can now just click "Enable macros" and no Automacros will run.
Excel 2007
Macro security set to low or trusted document
Note that this seems to fail if the trusted folder is on a network share!
If you have set your macro security to "Enable All Macros" or you have already set the document to trusted or the document is in a trusted folder, click File, Open, select your file and hold down the shift key when you click the Open button:
Fig. 4: Selecting the file from the File, Open dialog (Excel 2007)
Of course your file might be listed in the Most Recently Used files (MRU) list. In that case, hold shift when you click the file in the list:
Fig. 2: Clicking the file on the MRU (Excel 2007)
Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.
Macro security set to prompt
If your document is not in a trusted folder, has never been set to be trusted and macro security is set to something other than enable all macros to run, you can repeat what I've shown above.
Normally, if you open such a document, Excel will show the security bar. In this situation however, due to you holding down shift, Excel shows this window:
fig 3: Enable macros dialog (Excel 2007)
Because you held down the shift button when you clicked the file in the MRU or when you clicked the Open button, you can now just click "Enable macros" and no Automacros will run.
Excel 97, 2000, XP, 2003
Macro security set to low or trusted document
In case your macro security is set to low, or your VBA code is signed and you have set the publisher as trusted, you must hold down the shift key when you click the Open button on the File, Open dialog:
Fig. 7: Opening the file from the File, Open dialog (Excel 97-2003)
Of course your file might be listed in the Most Recently Used files (MRU) list. In that case, hold shift when you click the file in the list:
Fig. 8: Clicking the file on the MRU (Excel 97-2003)
Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.
Macro security set to medium or higher
If your macro security is set to al least "Medium" and you have never set it's publisher to trusted (for a signed macro) you can forget about holding shift when opening the file or clicking the file on the MRU list. Instead, you must hold shift when you enable macros:
Fig.9: Hold shift when you press Enable macros
Doing so will prevent the Workbook_Open event from firing and the Auto_Open macro from running.
Comments
All comments about this page:
Comment by: Jan Karel Pieterse (3/4/2010 7:25:07 AM)Hi Ashish,
You can open the file and enable macros, using the shift key trick shown in this article to prevent the open event from firing.
Then go into the VBE, locate the Workbook_Open sub and hit F8 to start debugging.
Comment by: Dominick (6/1/2010 9:26:51 AM)Macro 4.0 programs (written in the 90's) that have Auto_Open named to the first cell of code is NOT disabled by holding down the shift key when openning. Do you have any other suggestions to prevent the macro from firing off when the workbook is openned? Thanks.
Comment by: Dominick (6/1/2010 9:31:06 AM)Sorry. I didn't mention my working environments. Disabling Auto_Open macros using the Shift key worked fine up until Excel 2007. Please advise. Thank you.
Comment by: Jan Karel Pieterse (6/2/2010 12:41:58 AM)Hi Dominick,
I could not prevent Excel 2007, nor Excel 2010, from running the XLM Open event either. A bug if you ask me!
Comment by: Paul Olsavsky (7/5/2010 7:47:10 PM)I've tried all the suggestions, and I still can't stop autoopen macros from running by holding down "Shift". Is it posible that our crack network people have made some policy entry in the standard Office platform that they permit to run in our work environment?
Regards,
Paul O.
Comment by: Jan Karel Pieterse (7/6/2010 12:04:22 AM)Hi Paul,
As far as I know, policies cannot affect the effect of the shift key, but you may prove me wrong of course.
Are you positive you held down the shift key at the right moment (press BEFORE clicking the Open button and keep holding it down all the way through)?
Comment by: Theo Gevers (7/8/2010 12:07:46 AM)Is er al een oplossing omtrent het openen van excelsheets met een auto_open macro met behulp van het ingedrukt houden van de SHIFT toets? Bij werkt dit namelijk ook niet. Hoe kan ik deze functie herstellen?
Comment by: Jan Karel Pieterse (7/8/2010 11:17:34 AM)Hi Theo,
Answering in English....
It appears there is a bug in Excel 2007, where in these circumstances the shift key does NOT work to prevent auto macros from running:
- File is in a trusted folder
- Folder is on a network
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.