Most Valuable Professional


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

Subscribe to our mailing list

* indicates required
Newsletter Archive

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Events


Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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, 2013, 2016

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, 2013, 2016)

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 its 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 65 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (12/22/2014 11:08:58 AM)

Hi Brian,

I know, I find that annoying too. There isn't much you can do about it, other than:

- First opening Excel and following the instructions above
- Removing the file from the trusted files list (see my download page for a tool)
- Renaming the file first.

 


Comment by: Steve H (3/11/2015 7:32:07 AM)

I had the problem with the Auto_Open event still running when I opened the file from the windows file system, which as the developer I mostly do (rather than the 'file-open' method. I wanted to be mostly in 'developer' mode, but when I send out the file I wanted it to work in 'user' mode.

So - I simply include the following as the first line in the Auto_Open code:


    If Dir(ActiveWorkbook.Path + "\debug.txt") <> "" Then Exit Sub


In my working directory I then create an empty file called 'debug.txt' so for me the code never runs, but when I distribute the file it runs for everyone else.

 


Comment by: Jan Karel Pieterse (3/11/2015 9:38:17 AM)

Hi Steve,

Good idea. I have seen similar solutions, like a debug.txt file to enable debugging.

 


Comment by: Bill Benson (3/26/2015 6:21:27 PM)

I would like to know if there is a VBA driven approach to suppressing Auto_Open code in a workbook opened by a running macro in a current project. Often I am debugging code that another wrote and I do not want Excel settings messed with. What I am not sure about is whether turning EnableEvents to False will prevent code in a secondary workbook's Auto_Open procedure from running. I know it will suppress that workbook's Workbook_Open event code.

Thank you and good luck at the Summit.

 


Comment by: Jan Karel Pieterse (3/26/2015 6:25:32 PM)

Hi Bill,

Auto_Open routines never run when opening a workbook from VBA unless you use the runautomacros method, so you're safe.

Pity you couldn't make it to the Summit!

 


Comment by: Bill Benson (3/26/2015 7:28:22 PM)

Thank you for the quick response. Yes, the Summit would have been wonderful, but the timing - and the airline cost - was a bit prohibitive at this time. I am just starting a permanent job with Bob Umlas's firm, and he will be there. I hope he takes copious notes and comes back in a mood to share!!!! :)

 


Comment by: Tony (4/9/2015 10:46:03 AM)

I just wanted to say Thanks! I have been mashing my head against athe desk since yesterday morning because I didn't know that the left-shift trick works when opening Excel workbooks.

You have made me very happy. :)

 


Comment by: Rudi (6/24/2015 11:50:37 AM)

Thanks for the great tip.

 


Comments are temporarily disabled. We expect commenting to be re-enabled on August 1st, 2015

For simple Excel-related questions I advise this site: www.eileenslounge.com.

For projects and other questions, please contact me using my contact form.