Preventing auto_open and Workbook_Open events from running

Content

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 as a part of Microsoft 365

Recently, the behavior of Excel has changed in this area and it is no longer possible to prevent the workbook Open events from firing when you open an Excel file through the usual user interface methods as they are shown below.

Workaround 1, disable events

Open the VBA editor, press control+g

Paste this line into that window:

Application.EnableEvents = False

With your cursor on that line, press the Enter key. Now open the file in question.

Please note, that none of the application or workbook events will now fire so you'll have to set that value back to True to make that work again.

Workaround 2: Detect the shift key is down

If the workbook is yours and you just need a way to prevent the open event (or any other event for that matter) to do anything when the shift key is held down, you can use code similar to this:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If

Const SHIFT_KEY As Long = &H10

Function
ShiftPressed() As Boolean
'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Private Sub Workbook_Open()
    If ShiftPressed Then Exit Sub
    MsgBox "You did not hold down the shift key while the file was opened"
End Sub

Workaround 3: Detect a specific file in the same folder

Some colleagues of mine use this debugging technique. You simply place an empty textfile with a special filename into the same folder as the workbook. You can then use the Dir statement to detect whether that file is present:

If Len(Dir(ThisWorkbook.Path & "SomeMadeUPFileName.txt") > 0 Then
'Do something

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

 


Comment by: Sunny (9-7-2020 07:01:00) deeplink to this comment

Hi, problem of Excel 2007 file when re-opened stays no response. It froze after I ran vba to create 3 new workbooks and then close each one as indexed by Workbooks.count. I tried above solutions but no progress. No code in any Object events. File is .xlsb extension. TQ


Comment by: Jan Karel Pieterse (18-7-2020 19:25:00) deeplink to this comment

Hi Sunny,

If you want me to have a look you can send the file to me.


Comment by: John Nollett (13-8-2020 16:17:00) deeplink to this comment

In O365, I cannot prevent macros from running when opening an Excel file by holding down the shift key either.


Comment by: Jan Karel Pieterse (13-8-2020 17:23:00) deeplink to this comment

Hi John,

Thanks for reminding me. I have updated the article with a separate Microsoft 365 section.


Comment by: Patel Jay (10-9-2020 10:37:00) deeplink to this comment

How to enable macro when open any excel file using vba code


Comment by: Jan Karel Pieterse (24-9-2020 17:01:00) deeplink to this comment

Hi Patel,

If you open a file using VBA, the macro's are automatically enabled.


Comment by: Hans Troost (21-3-2023 16:28:00) deeplink to this comment

As a developer I often need to disable the workbook_open, when working on a project.
To solve this I developed a small excel-addin and assiged a button in Quick Access toolbar to each of the 2 macro's.

I'm using it for some years now, XL 2026, 2019, MS365. Very happy with it.
Possibly useful for some of you: feel free!

The code (from module export):

Attribute VB_Name = "HTools" ' Hans' tools

Dim myGridColor As Integer
Sub DisableEvents()
    Dim myWindow As Window
    Dim mySheet As Sheet1
    
    Set myWindow = ActiveWindow
    myGridColor = ActiveWindow.GridlineColor
    Application.EnableEvents = False
    For Each myWindow In Windows
        myWindow.GridlineColor = RGB(255, 0, 0)
    Next myWindow
    Workbooks(1).Saved = True
End Sub

Sub EnableEvents()
    Dim myWindow As Window
    Set myWindow = ActiveWindow
    Application.EnableEvents = True
    For Each myWindow In Windows
        myWindow.GridlineColorIndex = xlColorIndexAutomatic
    Next myWindow
    Workbooks(1).Saved = True
End Sub


Al the best, Hans Troost


Comment by: Jan Karel Pieterse (21-3-2023 16:39:00) deeplink to this comment

Thanks, Hans!


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.




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