How to disable Workbook_Open and Auto_Open in Excel

Workbook_Open and Auto_Open are two predefined events for Excel VBA. Both events allow you to run a macro right after the workbook is loaded. You may want to open the workbook without triggering anything in some cases - either to skip a certain code block for security reasons, or for programming purposes. This article will show you how to disable Workbook_Open and Auto_Open in Excel.

Let’s now take a closer look into how to disable Workbook_Open and Auto_Open:

Opening a workbook manually

You can use the following two approaches if you need to open a workbook manually.

Holding the Shift Key when opening

We’ll start with an easy approach to disable Workbook_Open and Auto_Open. If you hold down the Shift key when opening the file, the macros will not run. Beware that you should be in Excel’s Open window to make this work. This method doesn’t work when you open a file directly from your desktop or from the Windows Explorer. So,

1. Start with opening Excel (only Excel, not the workbook)
2. Activate the Open section - if you're not on that page already
3. Hold Shift Key
4. Click on the file
5. Release the Shift Key after the workbook is opened

Disabling all macros

An obvious way is to disable all macros using Excel Options. However, this method is the slowest approach so you may not want to turn on and off macros all the time.

Follow the steps below for disabling macros:

1. Click the File > Options.
2. Click Trust Center, and then click Trust Center Settings.
3. In the Trust Center, click Macro Settings.
4. Select either Disable all macros without notification or Disable all macros with notification
5. Click OK to apply the setting.

Opening a workbook from VBA

What if you need to open an Excel workbook with your VBA code? Disabling macros from Excel Options still works. Here is what you can do to disable Workbook_Open and Auto_Open when opening a workbook from a macro.

Disabling Events

Events are built in actions VBA which are executed automatically. Workbook_Open and Auto_Open are examples for two such events. As a result, disabling events can help you skip the Workbook_Open and Auto_Open events.

You can enable or disable events by assigning a Boolean value to the Application.EnableEvents.

• True: enables
• False: disables

Sample code:

Application.EnableEvents = False
Workbooks.Open "C:\..\..\...xlsm"
Application.EnableEvents = True

Macro Security Settings in VBA

The final approach is similar to the Disabling all macros method. However, this time we will show you how you can change the setting in a macro. You can change the Application.AutomationSecurity property’s value to mimic the setting in Excel Options.

The property can take three values:

• msoAutomationSecurityByUI. Uses the security setting specified in the Security dialog box.
• msoAutomationSecurityForceDisable. Disables all macros in all files opened programmatically without showing any security alerts.
• msoAutomationSecurityLow. Enables all macros. This is the default value when the application is started.

You want to use msoAutomationSecurityForceDisable to disable.

Sample code:

Sub Security()
    Dim secAutomation As MsoAutomationSecurity
    secAutomation = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Workbooks.Open "C:\..\..\...xlsm"
    Application.AutomationSecurity = secAutomation
End Sub