[Excel VBA] How to Disable Auto-Run Macros (Workbook_Open) When Opening a Workbook

When opening another workbook using VBA, if that workbook has a Workbook_Open event set, the macro will execute immediately upon opening. This can be problematic if you only want to read or write data but end up triggering message boxes or unexpected processes.

In this article, I will explain an essential technique to safely open a workbook by temporarily disabling these event macros using the Application.EnableEvents property.

What is Application.EnableEvents?

Application.EnableEvents acts like a “master switch for events” that controls whether Excel application events (macros automatically triggered by operations like Workbook_Open or Worksheet_Change) are enabled or disabled.

• Application.EnableEvents = False: Temporarily disables all events. In this state, Workbook_Open will not run even if you open a workbook.
• Application.EnableEvents = True: Re-enables events and returns Excel to its normal state.

This switch is very powerful, but you must be careful. If you set it to False and forget to set it back to True, buttons and other interactions may stop working, making Excel appear unresponsive.

Complete VBA Code (Recommended Pattern with Error Handling)

When disabling events, it is a golden rule to include error handling to ensure that events are re-enabled even if an error occurs during processing.

Sub OpenBookWithEventsDisabled()

    ' Declare variables
    Dim bookToOpenPath As String
    Dim openedBook As Workbook

    ' Specify the path of the workbook to open
    bookToOpenPath = ThisWorkbook.Path & "\EventDrivenWorkbook.xlsm"

    ' Check if the file exists just in case
    If Dir(bookToOpenPath) = "" Then
        MsgBox "The specified file was not found.", vbExclamation
        Exit Sub
    End If

    ' --- 1. Disable events ---
    Application.EnableEvents = False
    
    ' Prepare to ensure events are re-enabled even if an error occurs
    On Error GoTo ErrorHandler

    ' --- 2. Open the workbook without triggering events ---
    Set openedBook = Workbooks.Open(bookToOpenPath)
    
    ' --- Processing after opening ---
    MsgBox "Opened '" & openedBook.Name & "' without triggering events."
    openedBook.Close SaveChanges:=False
    '----------------------
    
' --- 3. Re-enable events ([Important] Must be executed) ---
ExitRoutine:
    Application.EnableEvents = True
    Set openedBook = Nothing
    Exit Sub
    
ErrorHandler:
    ' Jump here if an error occurs and display a message
    MsgBox "An error occurred: " & Err.Description, vbCritical
    ' Proceed to ExitRoutine to re-enable events
    Resume ExitRoutine
    
End Sub

Key Points of the Code

1. Disabling Events: Application.EnableEvents = False

Write Application.EnableEvents = False immediately before any process that might trigger an event, such as Workbooks.Open. This turns off Excel’s “event switch,” preventing macros like Workbook_Open from running when the workbook is opened.

2. Re-enabling Events: Application.EnableEvents = True

In the ExitRoutine:, always set Application.EnableEvents = True. If you forget this, Excel events will remain disabled even after the macro finishes, causing issues with manual operations.

3. Why is Error Handling Important?

If an error (like “File not found”) occurs during Workbooks.Open without error handling, the macro will stop right there. Consequently, the line Application.EnableEvents = True will never be executed, leaving events disabled indefinitely.

By using the On Error GoTo ErrorHandler syntax, you guarantee that the code under ExitRoutine: (re-enabling events) runs whether the process finishes normally or encounters an error. This structure is essential for using EnableEvents safely.

Summary

Here is the safe and reliable procedure for opening a workbook without triggering events like Workbook_Open:

1. Disable events using Application.EnableEvents = False.
2. Use On Error GoTo to ensure processing continues even if an error occurs.
3. Open the target workbook (Workbooks.Open).
4. Create an exit point (ExitRoutine) to ensure Application.EnableEvents = True is always executed, regardless of success or failure.

While Application.EnableEvents is a very convenient feature, its impact is wide-ranging. I strongly recommend always using it in combination with this error handling pattern.