Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Prevent Open Event
Deze pagina in het Nederlands

Preventing 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:

Selecting the file from the File, Open dialog

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:

Clicking the file on the MRU

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:

Enable macros dialog

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 and up

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:

Selecting the file from the File, Open dialog

Fig. 4: Selecting the file from the File, Open dialog (Excel 2007 and up)

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:

Clicking the file on the MRU

Fig. 2: Clicking the file on the MRU (Excel 2007 and  up)

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:

Enable macros dialog

fig 3: Enable macros dialog (Excel 2007 and up)

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:

Opening the file from 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:

Clicking the file on the MRU

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:

Hold shift when you press 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 55 in total (Show All Comments):

 


Comment by: Scott (4/7/2014 8:00:58 PM)

I have a macro that opens all the other workbooks in a folder that have macros to get some information about those files. I do not want the auto_open macros to run. However, they are running when the vb code opens them.

Supposedly, workbooks with auto_open macros will not automatically run if opened with vb code. I find this not to be true. They are running and I don't want them to. I wish to find a way to prevent this.

 


Comment by: Jan Karel Pieterse (4/9/2014 7:22:59 AM)

Hi Scott,

You could turn off macros of those files entirely:

Application.AutomationSecurity=msoAutomationSecurityForceDisable

 


Comment by: Arthur (6/24/2014 5:34:59 PM)

I have a file with macros, I have a specific macros that will delete certain information on the spread sheet if it's opened after a certain date, however I want to know if there's a way that if the user "disables macros" then the file will not open.

I get that a macro could do SO much stuff that that's why it's a security issue, but the security issue goes with wanting to make sure that if someone opens the file after a certain date, that certain info on the spread sheet gets deleted. *using the macro

or could i do the deleting with "conditional formatting"?

 


Comment by: Jan Karel Pieterse (6/25/2014 11:36:32 AM)

Hi Arthur,

The way this is often done is like so:

- Add a sheet which shows a message to the user about having to enable macros, add screenshots how to do it on that sheet too, as some people don't know that.

- In the BeforeSave event in the Thisworkbook module, make sure all sheets are hidden, except this macro warning sheet.

- At the end of the beforesave event, schedule a sub in a normal module that does the opposite (show all sheets, hide the macro warning sheet), so that when the user saves, he saves a file with just the macro sheet visible, but that when the save is done the normal state is restored.

- In the Workbook_Open event in ThisWorkbook, call that same scheduled macro that shows al sheets.

 


Comment by: David Evans (9/3/2014 10:53:33 AM)

Hi
I have one or two blank workbooks opening each time I open excel - I have exhausted all suggestions on many forums without luck, including the above - getting pretty desperate now as when I start to type formulas in a workbook the blank ones open preventing me from seeing the original page, they also grow in number and I end up getting a not enough resources to complete the task error even for simple tasks.
I have nothing in XLstartup, I have deleted xlb files but they reappear

Really hope you can help
Dave

 


Comment by: Jan Karel Pieterse (9/3/2014 11:53:27 AM)

Hi David,

Have a look at this page, perhaps it gives you some pointers:
http://www.jkp-ads.com/Articles/StartupProblems.asp

 


Comment by: Dave (9/30/2014 9:19:24 PM)

Hi Jan -

I have 4 macros in one workbook which are currently run manually on a monthly basis. Step 1 must be completed before Step 2 can be run before Step 3 can be run, etc.
I'd like to move to running these daily and with as little human intervention as possible. However, I can't nest Steps 2, 3 and 4 in Step 1 and just put them on "Auto_Open" b/c Step 1 calls on a non-Windows based program to update stock prices and other similar data. Depending on the amount of data, this can take several minutes, but it HAS to be finished before Step 2 can engage, or Step 2 will just be looking @ a bunch of cells with N/As, which will foul up Steps 3 and 4.

The best case scenario for me would be as follows:
1) Step 1 kicks off @ 00:00:00 (i.e. midnight)
2) I nest Steps 3 and 4 in Step 2 which kicks off 2 hours later (plenty of time for all data to update).

Is something like this doable?
Thanks.

 


Comment by: Jan Karel Pieterse (10/1/2014 10:23:27 AM)

Hi Dave,

I think your best bet would be to separate out the step that updates the external program into a new workbook so you can schedule that one prior to the other steps.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].