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
Showing last 8 comments of 18 in total (Show All Comments):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
Comment by: Jeff Whale (7/20/2010 12:41:19 PM)Sort of related question to Open events
I have a workbook that has 3 separate modules
The main one creates an output book by copying sheets as required
It also copies one of its modules into the new workbook by
ActiveWorkbook.VBProject.VBComponents("Module2") _
.Export WorkMacro
then later
With WorkObject.VBComponents
.Import WorkMacro
End With
Kill WorkMacro
This works fine but I'd like to also create an open and a close event macro in the output workbook and don't know how to do this
ASny tips please ?
Comment by: Jan Karel Pieterse (8/16/2010 2:56:27 AM)Hi Jeff,
Here is what I would do:
- Create a template workbook with all code in place
- copy the sheets to that template workbook
Comment by: Jeff Whale (8/17/2010 1:59:12 PM)Your reply was :-
Comment by: Jan Karel Pieterse (8/16/2010 2:56:27 AM)
Hi Jeff,
Here is what I would do:
- Create a template workbook with all code in place
- copy the sheets to that template workbook
Thanks Jan - yep, already thought that would work but the problem is that the template is run by many people at different locations and they'd all need a new template that would need to be kept up to date
Anyway, it's an interesting academic exrecise - if you happen to discover a way it would be really good
Thanks again
Comment by: Jan Karel Pieterse (8/18/2010 12:13:47 AM)Hi Jeff,
True, distributing two files is more work than one.
But is that more cumbersome than telling all of the users they have to change their macro security settings to allow access to the VBA project?
Comment by: Jeff (1/4/2012 4:26:40 PM)Thank you so much!
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.