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.
- Workbook_Open should be in ThisWorkbook Auto_Open can be in any module.
- Excel triggers Workbook_Open before Auto_Open.
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,
- Start with opening Excel (only Excel, not the workbook)
- Activate the Open section - if you're not on that page already
- Hold Shift Key
- Click on the file
- 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:
- Click the File > Options.
- Click Trust Center, and then click Trust Center Settings.
- In the Trust Center, click Macro Settings.
- Select either Disable all macros without notification or Disable all macros with notification
- 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.
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
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.
Sub Security() Dim secAutomation As MsoAutomationSecurity secAutomation = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityForceDisable Workbooks.Open "C:\..\..\...xlsm" Application.AutomationSecurity = secAutomation End Sub