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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
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 82 in total (Show All Comments):

 


Comment by: Meme (4/13/2016 4:57:48 PM) Direct link to this comment

Hi Jan, you're right. That's the correct way to save it. But VBA will also open with saving it as xls, but not as xlsx.

 


Comment by: Ben (4/26/2016 12:12:02 PM) Direct link to this comment

Hi

I have been trawling the internet trying to find someone to help me and although my question is slightly of the topic of your post I am hoping as someone who clearly has a great deal of skill in VBA, perhaps you would help.

I am trying to write a piece of VBA code in excel that on open of the workbook, checks if the VBA password is still in place and not missing or been edited (by someone hacking it), and then if is missing etc, self delete the excel file.

Thank you for your time and help, I really appreciate it.

Best Regards

Ben

 


Comment by: Jan Karel Pieterse (4/26/2016 1:29:16 PM) Direct link to this comment

Hi Ben,

I'm afraid this isn't something you can easily do, at least the user must have allowed access to the VBA project.
Moreover, if the user has accessed your code, it is likely that the code that should remove the add-in is already removed by the user. Better to prevent access to the VBA code to start with, for example by using UnviewablePlus:
http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html

 


Comment by: Himanshu (5/3/2016 6:17:12 PM) Direct link to this comment

Hello,

I have an excel workbook which is macro enabled.

I believe there is something wrong with its coding, due to this file hangs while opening. For the last two days it is not opening at all, I can only see a blank screen and rotating cursor. It is also returning a message that "Excel is not responding". This workbook is important for me. Please can you help me to open this file. if the issue can't be resolve, please suggest me a way so that I can at least copy my macro coding, it will help me to save my efforts.

Thanks
Himanshu

 


Comment by: Jan Karel Pieterse (5/4/2016 2:34:51 PM) Direct link to this comment

Hi Himanshu,

Have you seen this page perhaps: http://www.jkp-ads.com/Articles/CorruptFiles.asp

 


Comment by: Arjen (5/8/2016 6:52:47 AM) Direct link to this comment

Hello Jan,
my question is also slightly of the topic but i hope you can answer this.

I have made an auto_open file (xlsm) and set macro security to “Enable all macros”.
If I open this file in the normal explorer or via another VBA command “ShellExecute”, excel will open with an empty screen. The macro will not be executed…?
Also a shortcut on my desktop screen results in a nothing happens event???

The only way I can start the program including its macro event is that I have to open the MRU (most recently used files) and click once on the name…..
PC is windows 7 and Excel version is 2016

By the way, I have tested the same file on a windows 8.1 and Excel 2013 and it will work normally.
Hope you can help,

Thanks in advance, Arjen

 


Comment by: Jan Karel Pieterse (5/11/2016 3:57:23 PM) Direct link to this comment

Hi Arjen,

Have a look at this article, it may contain a solution for this problem: www.jkp-ads.com/articles/startupproblems.asp

 


Comment by: Leon (6/3/2016 12:04:01 PM) Direct link to this comment

one little note to add on this topic - holding shift while opening the file only works when opening in Excel.

I don't think there's a way to open an xlsm file without the macro running if you open it in My Computer, for example.

If I'm wrong, I'd welcome a solution..

thanks, Leon

 


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].