VBA Code Workbook Open: How to Run Macros at Startup

To run VBA 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 procedure runs when the workbook opens if macros are permitted; it does not run in Excel for the web.

What the Workbook_Open Event Does

Workbook_Open is an Excel workbook event: a procedure that Excel calls in response to a particular action. Here, that action is opening the workbook. It is useful for setup tasks that should happen automatically, such as displaying a welcome message, updating a dashboard timestamp, checking that required sheets exist, or preparing a workbook for use.

The event belongs to one workbook. It is not a general macro that runs every time Excel starts, and it does not run merely because you switch to the workbook if it is already open. It also does not run when you open a worksheet within a workbook. For actions tied to selecting a sheet or changing a cell, other worksheet events are relevant.

One important distinction in VBA is ThisWorkbook versus ActiveWorkbook. ThisWorkbook means the workbook containing the code. ActiveWorkbook means whichever workbook is currently in front. Startup code should usually refer to ThisWorkbook, because another workbook or an add-in could become active while the code runs.

Where to Put the VBA Code

Place the event procedure in the workbook object’s code module—not in a standard module and not in a worksheet module. In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. If the Developer tab is visible, you can also choose Developer > Visual Basic.

In the Visual Basic Editor, find the Project Explorer. If it is hidden, press Ctrl+R on Windows.
Expand the project for the workbook you want to automate.
Under Microsoft Excel Objects, double-click ThisWorkbook.
Paste the event procedure into the code window that opens.

If the Project Explorer contains several open workbooks, check the project name before pasting. It is easy to put code in the wrong workbook, especially when a personal macro workbook or another file is open. On Excel for Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard and function-key settings. Excel for the web does not provide the desktop VBA editor or run VBA macros.

How to Add and Test a Startup Macro

For a practical example, the following event writes the current date and time into cell B2 on a sheet named Dashboard. It uses ThisWorkbook so it targets the workbook that contains the code, and it displays a helpful message if the expected sheet is missing or the update otherwise fails.

Private Sub Workbook_Open()
    Dim ws As Worksheet

    ' Update the dashboard timestamp when this workbook opens.
    On Error GoTo OpenError
    Set ws = ThisWorkbook.Worksheets("Dashboard")
    ws.Range("B2").Value = Now
    Exit Sub

OpenError:
    MsgBox "Startup could not update the dashboard: " & Err.Description, _
        vbExclamation, "Workbook startup"
End Sub

Change Dashboard and B2 to match your workbook, or adapt the action to a task you actually need. The named sheet must exist. Because this example writes a value, it replaces anything already in B2; it does not merely display the time. If you use a cell with a date-and-time number format, the timestamp will be easier to read.

To test it, save the code, save the workbook, close the workbook, and open it again in desktop Excel with macros enabled. A quick test while the code window is open is not equivalent: the event is triggered by opening the workbook, not by pressing Run on the event procedure. If the code needs debugging, you can temporarily call a separate test procedure or use breakpoints, but avoid changing the event’s signature.

Save in a Macro-Enabled File Format

VBA code requires a file format that preserves macros. In Excel for Windows, choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm), and save. The standard .xlsx format cannot store VBA project code. If Excel warns that the workbook contains features that cannot be saved in a macro-free workbook, choosing to continue without macros removes the code from the saved copy.

Excel also supports macro-enabled binary workbooks (.xlsb) and macro-enabled add-ins, but those formats serve different purposes. For an ordinary workbook that needs an open event, .xlsm is usually the clearest choice. Make sure the file you reopen is the macro-enabled copy, not an older .xlsx version with the same base name.

Before running or changing startup code, save a separate copy of the workbook. A macro can change cells, refresh data, or perform other actions, and macro changes cannot be reversed with Ctrl+Z. On Mac, the corresponding undo shortcut is generally Command+Z, but it does not undo VBA actions either.

Macro Security and Safe Use

Excel may block macros because VBA can make changes to files and interact with other parts of the system. A workbook’s code does not automatically run just because it contains a Workbook_Open procedure. Excel’s security settings, the file’s source, and any organizational policies affect whether macros are allowed.

When a trusted workbook opens with macros disabled, Excel may show a security notification. Only enable content if you trust the file and understand why it needs macros. Do not lower security globally or enable all macros simply to make one unfamiliar workbook run. For a workbook you control, a trusted location may be an option, but only use a folder where you control what files are placed.

On Windows, review settings through File > Options > Trust Center > Trust Center Settings > Macro Settings. Options can differ by organization, and a work or school administrator may manage them. On Mac, macro security is managed in Excel’s application preferences or settings, and the exact labels can vary by release. If a company policy blocks the file, ask the administrator rather than trying to bypass the control.

Before trusting a workbook from someone else, consider whether the requested startup behavior makes sense. A macro that updates a visible dashboard is different from one that silently changes many files. If you are unsure, do not enable it; ask the sender for a description or use a macro-free version.

Workbook_Open Compared with Auto_Open

Older Excel workbooks sometimes use a macro named Auto_Open in a standard module. It is a legacy auto-run convention, while Workbook_Open is an event procedure attached directly to the workbook. For new VBA work, the event is generally the more direct and maintainable way to respond to the workbook opening.

There is also a practical behavior difference: an Auto_Open macro is not run in the same way when a workbook is opened programmatically with VBA’s Workbooks.Open method. The workbook’s open event is the appropriate mechanism when code should respond to a workbook being opened, including in common automation workflows where events are enabled. Neither method overrides macro security, and both require code that Excel can access.

Do not create both mechanisms for the same startup task unless you deliberately want both to run. Duplicating actions can cause confusing results—for example, the same refresh or message appearing twice. If you inherit a workbook with Auto_Open, inspect its code and test carefully before replacing or combining it with an event procedure.

Common Reasons the Event Does Not Run

If nothing happens on opening, check the likely causes before rewriting the code. The procedure name and its location matter, and Excel must be allowed to run macros.

The code is in the wrong module. Confirm it is under the correct workbook’s ThisWorkbook, not in Module1 or a sheet module.
The file is not macro-enabled. Reopen the .xlsm copy and check whether the code is still present in the Visual Basic Editor.
Macros are blocked. Review the security notification and your organization’s policies. Do not enable code from an untrusted source just to test it.
The workbook was already open. The event runs when Excel opens the workbook; selecting its window again does not trigger another open event.
Events are disabled. VBA can turn off application events, and poorly handled code may fail to turn them back on. If events were disabled in the current Excel session, other workbook events may also stop responding.
The procedure has a different name or signature. Use the event procedure generated by Excel’s object and event selectors, and do not add parameters to it.
The startup action has an error. A missing sheet, protected range, or invalid reference can stop the procedure. Use the Visual Basic Editor’s error message and the VBA editor’s debugging tools to identify the failing line.

In the Visual Basic Editor, you can generate the correct event shell by selecting Workbook from the left object drop-down above the code window, then selecting Open from the right procedure drop-down. If an existing open procedure is already there, edit that one rather than creating a duplicate. After fixing code, close and reopen the workbook to test the actual startup sequence.

Choosing What Belongs in Startup Code

Keep an open event short and predictable. It is a good place for small initialization steps that make the workbook ready to use. It is a poor place for a long chain of actions that takes a long time, changes many files, or requires the user to make decisions before seeing the workbook. A slow or unexpected startup routine can make a workbook appear frozen and can frustrate people who only need to read its contents.

For larger tasks, consider showing a clear message and letting the user start the work explicitly with a button, or divide the process into smaller procedures that can be tested independently. If code refreshes external data, remember that network access, credentials, privacy settings, and connection permissions can affect the result. The event itself does not guarantee that every external connection will refresh successfully.

Also consider what happens when several people use the same file. Startup code should not assume every user has the same folder paths, printer, data connections, or Excel settings. Use clear error messages, avoid hard-coded personal paths where possible, and document what the macro changes. If the workbook is shared, tell users that it contains VBA and what the startup behavior is before they enable it.

Frequently Asked Questions

Does Workbook_Open run automatically every time I open the file?
It runs when Excel opens the workbook if VBA macros are allowed and application events are enabled. It will not run simply because you switch to an already-open workbook, and Excel for the web does not run VBA.

Can I put Workbook_Open in Module1?
No. Put the event procedure in the workbook’s ThisWorkbook module. A procedure with the same name in a standard module is not the workbook’s open event.

Why does my .xlsx file lose the macro?
The .xlsx format does not preserve VBA project code. Save a macro-containing workbook as .xlsm or another suitable macro-enabled format, then reopen that saved copy.

Can I run Workbook_Open in Excel for the web?
No. Excel for the web can open and edit many workbooks, but it cannot run VBA macros. Open the file in the Excel desktop application to use its VBA code, subject to macro security settings.

How do I run the startup code again without closing Excel?
The event is designed to run on opening, so the straightforward test is to save and close the workbook, then open it again. If you need a repeatable action while the file remains open, move that work into a separate public procedure and provide a button or another deliberate way to run it.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.