Yes. To run a VBA macro when an Excel workbook opens, put a Workbook_Open event procedure in that workbook’s ThisWorkbook code module, then save the file as an Excel Macro-Enabled Workbook (.xlsm). The macro runs when the workbook opens in desktop Excel only if macros are allowed; Excel for the web does not run VBA.
How the workbook-open event works
Workbook_Open is an event: Excel calls it when a particular workbook opens. Unlike a regular macro, it is not normally started by clicking a button or choosing it from the Macros dialog. Its location matters: the procedure belongs in the workbook’s ThisWorkbook object in the Visual Basic Editor (VBE), not in a worksheet code module or an ordinary module.
The event runs once for each opening of that workbook in an Excel desktop session, subject to security settings and any conditions in the code. It is useful for actions such as displaying a brief welcome, selecting a landing sheet, setting a few workbook-specific options, or calling a startup routine. It does not mean that a macro runs simply because the file is saved on a computer or because a browser previews it.
There is also an older auto-start mechanism named Auto_Open. For most workbook-specific startup tasks, Workbook_Open is the clearer choice because it is an Excel workbook event and can be placed with the other events for that workbook. The distinction and a few special cases are covered below.
Set up Workbook_Open in Excel for Windows
- Open the workbook in desktop Excel. If the file is important, save a separate copy before adding or running code.
- Press Alt+F11 to open the Visual Basic Editor. If the Project Explorer is not visible, press Ctrl+R.
- In Project Explorer, find the workbook project, expand Microsoft Excel Objects, and double-click ThisWorkbook.
- Paste the event procedure into the code window for ThisWorkbook, not into a sheet such as Sheet1 and not into a standard module.
- Return to Excel and use File > Save As. Choose Excel Macro-Enabled Workbook (*.xlsm) as the file type.
- Close the workbook and reopen it to test the event. If Excel displays a security warning, enable macros only when you trust the workbook and its source.
This example activates a worksheet named Start if that tab exists. Change the name in the code to match your own sheet, or use another startup action.
Private Sub Workbook_Open()
Dim ws As Worksheet
' Activate the optional landing sheet when the workbook opens.
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Start" Then
ws.Activate
Exit For
End If
Next ws
End Sub
The procedure does nothing if there is no worksheet named Start; it does not create one. The name must match the worksheet tab, including spaces. If you want the event to perform a larger task, keep the event short and have it call a clearly named public procedure in a standard module. That makes the startup trigger easier to inspect and the main task easier to run or maintain separately.
Choose what the startup macro should do
Opening a workbook is a convenient trigger, but it is not always the right place for every task. A startup macro can interrupt the user, take time, or make unexpected changes before the person has reviewed the file. Consider whether an action should happen automatically, or whether a button or manual macro gives the user better control.
- Good startup candidates: activate a welcome or dashboard sheet, set a workbook-specific display state, or show a concise reminder about the file.
- Use caution: refresh external data, send messages, modify many cells, delete or replace content, or run code that depends on network access. These actions may take time, fail because a service is unavailable, or surprise someone opening the workbook.
- Prefer a prompt or button: when the user should decide whether to proceed, ask for confirmation or provide a clearly labelled control rather than running an irreversible action automatically.
VBA actions generally cannot be reversed with Excel’s Undo command (Ctrl+Z on Windows). Save a copy before testing code that edits data, and use a sample workbook if the macro will make substantial changes. A macro that only activates a sheet is low impact; a macro that overwrites records is not.
Save the right file type and test the event
A workbook saved as .xlsx cannot store VBA project code. If you try to save a macro-containing workbook in that format, Excel warns that the VBA project will be removed if you continue. Choose .xlsm to retain macros. The file extension does not itself make the code run: Excel still applies its macro-security rules when the file opens.
To test properly, save the workbook, close it, and reopen it in desktop Excel. Running the code manually from the VBE is useful for checking the procedure, but it does not prove that the open event is connected correctly. Also test the case where the named landing sheet is absent if your workbook may be renamed or customized. For production files, test a copy rather than the only working version.
To display the Developer tab in Excel for Windows, use File > Options > Customize Ribbon, then select Developer under Main Tabs. The Developer tab is helpful for opening VBA tools and managing macros, but it is not required for the Workbook_Open event to work.
Macro security and safe opening
Excel may block macros from files downloaded from the internet or received through email, and organizations can impose additional policies. A security banner or Protected View can therefore prevent startup code from running. Do not respond by enabling all macros globally. Only enable code in a workbook whose source and purpose you trust, and check with your organization’s administrator if policy controls the setting.
For a file you created or reviewed, follow the security prompt provided by Excel if it offers an appropriate option. A trusted location may be available in managed environments, but adding a location makes files there more trusted, so it should be used deliberately. If a workbook unexpectedly asks for permission to run code, treat that as a reason to verify who supplied it and what the code does, not as a routine step to click through.
VBA is powerful: a macro can change workbook data and, depending on its code and permissions, interact with files or other resources. The event procedure itself is not a security bypass. If macros are disabled, the event will not run; if a file is blocked by policy, changing its code placement will not override that policy.
Common reasons the macro does not run
- Code is in the wrong module. Move the event procedure to the workbook’s ThisWorkbook object. A procedure in a worksheet module or standard module will not respond to this workbook-open event just because its name is similar.
- The file was saved as .xlsx. Save as
.xlsmand confirm that the VBA project is retained. - Macros are disabled or blocked. Check Excel’s security message and any organizational policy. Do not weaken security for an unknown file.
- The workbook was not closed and reopened. The event is triggered by opening the workbook, not by editing the code or pressing Save. Close and reopen it for a realistic test.
- Events have been turned off. Other VBA code can set
Application.EnableEventstoFalse. If that state is left in effect, Excel events may not fire. In the Immediate window of the VBE, a trusted user can check or restore the setting by enteringApplication.EnableEvents = Trueand pressing Enter. - The event contains an error or a condition is not met. A spelling mismatch, missing worksheet, or runtime error can stop the intended action. Step through the code in the VBE and review its error messages.
Be careful when restoring Application.EnableEvents: the setting applies to the Excel application, not just one workbook. Code that temporarily disables events should restore them even if an error occurs. Avoid adding broad error suppression merely to hide a startup failure; it can make a problem harder to find.
Workbook_Open compared with Auto_Open
Workbook_Open is the standard event-based approach for reacting to a workbook being opened. Auto_Open is a legacy-style auto macro that can be placed in a standard module. Some older workbooks and automation workflows still use it, and Excel supports auto macros in certain circumstances. However, mixing both approaches can cause the same startup action to run twice, so choose one approach unless there is a specific compatibility reason.
Another distinction is that a workbook event is tied to that workbook’s object model, while ordinary procedures in standard modules are general macros. This makes Workbook_Open a natural place for the trigger and a standard module a sensible place for reusable work. If another macro opens a workbook while events are disabled, its Workbook_Open event will not fire until events are enabled; it is not a reliable substitute for explicitly calling a procedure in a controlled automation sequence.
Excel for Mac and Excel for the web
VBA is supported in desktop Excel for Mac, and workbook events such as Workbook_Open can be used there as well. The Visual Basic Editor and some keyboard shortcuts differ from Windows; on Mac, open the VBE through Tools > Macro > Visual Basic Editor where available. Keyboard function-key behavior can depend on the Mac’s keyboard settings, so use the menu if a Windows shortcut does not apply. The workbook must still be saved in a macro-enabled format.
Excel for the web does not create, edit, or run VBA macros. A workbook with macros can be stored and opened in the browser, but the VBA event will not execute there. To run the VBA, open the file in desktop Excel. If users need browser-based automation, investigate Office Scripts and the automation features available in their Microsoft 365 environment; Office Scripts are a different technology, not VBA translated automatically.
Frequently Asked Questions
Can I run a macro automatically when an Excel file opens?
Yes. Put a Workbook_Open procedure in that workbook’s ThisWorkbook module, save as .xlsm, and open it in desktop Excel with macros permitted.
Why does Workbook_Open not appear in the Macros list?
It is an event procedure, not a regular macro intended to be launched from the Macros dialog. Check it in the ThisWorkbook module and test it by closing and reopening the workbook.
Can Workbook_Open run in Excel for the web?
No. Excel for the web does not run VBA. Open the macro-enabled workbook in desktop Excel to run its VBA code.
Can I run more than one macro when the workbook opens?
Yes. The event can call a single startup procedure that performs several tasks, or call multiple procedures in sequence. Keep the sequence clear, and consider how errors or slow operations affect the opening experience.
Will the macro run if I save the workbook as .xlsx?
No. The .xlsx format does not retain VBA code. Save the workbook as .xlsm if it needs to keep and run its macros.
Related
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
- Excel Shuffle List of Names: 10 Easy Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Math Functions: What They Do and How to Use ThemExcel VBA includes built-in math functions such as Abs, Sqr, Log, Sin, and Round, which you can call…
- Excel VBA: Open XML Files, Read Data, and Handle ErrorsYes. In Excel VBA on Windows, use Workbooks.OpenXML to open an XML file in Excel’s workbook format, or…
- VBA Range Column: How to Reference ColumnsIn VBA, reference a whole worksheet column with Columns("C") or Range("C:C"); to get the column containing a particular…
- Excel VBA UserForm Label Font Color: Set and ChangeTo change an Excel VBA UserForm label’s text color, set its ForeColor property. For example, in the UserForm’s…
- VBA Check If a Worksheet Is ProtectedIn VBA, check a worksheet’s ProtectContents property: Worksheets("Data").ProtectContents returns True when that worksheet’s cell contents are protected and…
- Excel Worksheet Change Event: VBA Setup and ExamplesExcel’s Worksheet_Change event is a VBA procedure that runs when cells on a particular worksheet are changed, such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.