VBA Workbook_Open in Excel — Run a Macro Automatically When the File Opens (and Where the Code Must Live)

TL;DR — Workbook_Open is an event procedure: Excel calls it for you the moment the file finishes opening, so a macro can run itself with no button and no user action. Two things make or break it. First, it must live in the ThisWorkbook object, not in a standard Module — paste it into Module1 and it simply never fires. Second, it only runs when macros are enabled and the file is a macro-enabled workbook (.xlsm/.xlsb); if the user opens it with macros blocked, nothing happens and no error is shown.

' This code lives in the ThisWorkbook object (double-click "ThisWorkbook"
' in the Project Explorer — NOT in Module1).
Private Sub Workbook_Open()
    Worksheets("Dashboard").Activate
    Range("A1").Select
    MsgBox "Welcome back — data last refreshed " & Format(Now, "dd mmm, hh:nn")
End Sub

Most macros wait for a button, a shortcut, or the Macros dialog. An event macro is different: you never call it — you register it, and Excel runs it when something happens. Workbook_Open is the first event most people meet, because "do this every time the file opens" is such a common need: jump to the dashboard, refresh a query, set up the UI, stamp an open-log. It's also the event people most often can't get to fire — almost always for one of two reasons this article makes unmissable.

What you'll learn

The mental model — an event handler you register, not a macro you run
The one rule that decides everything — the code must live in ThisWorkbook
Why it silently never runs — macro security and the wrong file format
Workbook_Open vs the legacy Auto_Open, and which to use
Why event code must be fast and crash-proof — it greets the user on every open

The mental model: an event handler you register, not a macro you run

A normal macro is a verb you invoke: you press a button and Sub RefreshData runs. An event procedure is the opposite — you write it once, put it in a specific place with an exact name, and then Excel decides when to call it. You don't run Workbook_Open; you promise Excel "here's what to do when this workbook opens," and Excel keeps that promise for you.

That flips how you think about "where does this code go." For a normal macro the location barely matters. For an event, the location is the registration. Excel looks for Workbook_Open in exactly one place — the workbook's own code module, called ThisWorkbook — and nowhere else. The name and the place together are the whole contract.

The rule that decides everything: it must live in ThisWorkbook

This is the number-one reason a Workbook_Open "doesn't work": the code was pasted into a standard module. Workbook_Open is a member of the workbook object, so its handler has to live in the workbook's code module — ThisWorkbook — not in Module1.

Project Explorer (Ctrl+R in the VBA editor)
 └─ VBAProject (YourFile.xlsm)
     ├─ Microsoft Excel Objects
     │   ├─ Sheet1 (Sheet1)      ← worksheet events go here
     │   └─ ThisWorkbook         ← Workbook_Open goes HERE (double-click it)
     └─ Modules
         └─ Module1              ← a Workbook_Open here NEVER fires

There's a fast way to get the skeleton right every time: open the ThisWorkbook code pane, pick Workbook in the left-hand (Object) dropdown, then Open in the right-hand (Procedure) dropdown. Excel writes the exact signature for you:

Private Sub Workbook_Open()

End Sub

The signature is fixed. It's Private Sub Workbook_Open() — no arguments, spelled exactly, in ThisWorkbook. Rename it, add a parameter, or move it, and it stops being the event handler and becomes an ordinary (never-called) sub. The rule: if an auto-open macro won't fire, check its location first — 90% of the time it's in a Module instead of ThisWorkbook.

The rule for why it silently never runs: macros must be enabled

Even in the right place, Workbook_Open runs only if Excel is allowed to run macros — and when it isn't, there is no warning and no error. Three things quietly switch it off:

The file isn't macro-enabled. VBA only survives in .xlsm or .xlsb. Save a workbook that has code as a plain .xlsx and Excel strips every macro — including Workbook_Open — with only a passing prompt. The event is simply gone.
Macros are disabled by security. If the user opens the file and leaves it in Protected View, or clicks past the "Enable Content" banner without enabling, macros don't run, so the event doesn't fire.
Events are turned off at the application level. If some earlier code set Application.EnableEvents = False and never restored it, workbook and worksheet events stay suppressed for the whole session.

The design lesson matters: never make data correctness depend only on Workbook_Open. It's perfect for convenience — jump to a sheet, refresh a view — but if a user opens the file with macros off, your "always runs" code didn't. Treat it as a nice-to-have that improves the experience, not a guarantee. (If your goal is just to get users past the security banner, see how to enable macros in Excel.)

Workbook_Open vs Auto_Open: use the event, not the relic

You'll see two ways to run code on open, and they are not the same thing:

Auto_Open is the legacy (Excel 5/95-era) mechanism. It's a plain Sub Auto_Open() that lives in a standard Module. It still works for backward compatibility, but it's a relic.
Workbook_Open is the modern event, living in ThisWorkbook.

They differ in ways that bite:

	`Workbook_Open` (event)	`Auto_Open` (legacy)
Where it lives	`ThisWorkbook`	a standard `Module`
Fires on `Workbooks.Open` (opened by code)	Yes	No (needs `.RunAutoMacros`)
If both exist	runs first	runs after
Status	current, recommended	backward-compat only

The one to remember: if another macro opens your file with Workbooks.Open "Report.xlsm", Workbook_Open fires but Auto_Open does not (unless you explicitly call wb.RunAutoMacros xlAutoOpen). That difference alone is why automated pipelines break on Auto_Open. Write Workbook_Open; reach for Auto_Open only to maintain very old files.

The rule that keeps it from ruining every open: be fast and crash-proof

Workbook_Open runs before the user can do anything — so whatever it does, the user experiences it as "how long the file takes to open" and "whether the file even opens cleanly." Two habits keep it civilised:

Private Sub Workbook_Open()
    On Error GoTo Fail                 ' never let the open crash on the user
    Application.ScreenUpdating = False

    Worksheets("Dashboard").Activate
    Range("A1").Select

    Application.ScreenUpdating = True
    Exit Sub
Fail:
    Application.ScreenUpdating = True   ' always restore state, even on error
    MsgBox "Startup skipped: " & Err.Description, vbExclamation
End Sub

Always wrap it in error handling. An unhandled error here throws a raw VBA error in the user's face the instant they open the file, and can leave settings like ScreenUpdating or EnableEvents switched off. Restore state in the handler. (This is the same discipline covered in VBA On Error.)
Keep heavy work out of it, or make it visible. A slow query or a big loop in Workbook_Open looks exactly like a frozen file. If work is unavoidable, show a status message or move it behind a button the user chooses to press.

Workbook_Open is one of a whole family of workbook events — Workbook_BeforeClose, Workbook_BeforeSave, Workbook_SheetChange — all living in ThisWorkbook. Its sheet-level cousins react to what happens inside a sheet: Worksheet_Change when a cell is edited, and Worksheet_SelectionChange when the cursor moves.

How ExcelMaster helps

Wiring up Workbook_Open is a small ritual with sharp edges: right object, exact name, macro-enabled file, error handling, don't hang the open. Get any one wrong and the symptom is the same unhelpful "nothing happened."

ExcelMaster lets you describe the outcome instead. Say "every time this file opens, jump to the Dashboard sheet and refresh the pivot," and it writes the handler, places it in ThisWorkbook, and wraps it so a failure won't crash the open. You still own the file and can read every line — but you skip the part where a macro silently refuses to run because it landed in Module1.

Frequently asked questions

Where does Workbook_Open code go in Excel?

In the ThisWorkbook object, not in a standard module. Open the VBA editor (Alt+F11), find ThisWorkbook under your project's "Microsoft Excel Objects," double-click it, and put Private Sub Workbook_Open() there. A Workbook_Open sub placed in Module1 looks identical but never fires, because Excel only looks for the event in the workbook's own code module.

Why doesn't my Workbook_Open macro run?

Almost always one of three things: the code is in a Module instead of ThisWorkbook; the file was saved as .xlsx (which strips all macros) instead of .xlsm; or the user opened it with macros disabled and didn't click "Enable Content." Check the location first — it's the most common cause. Also confirm no earlier code left Application.EnableEvents = False.

What is the difference between Workbook_Open and Auto_Open?

Workbook_Open is the modern event, living in ThisWorkbook. Auto_Open is the legacy macro, living in a standard module. The key practical difference: Workbook_Open fires when the file is opened by another macro (Workbooks.Open), but Auto_Open does not unless you call RunAutoMacros. Use Workbook_Open; keep Auto_Open only for maintaining old files.

Does Workbook_Open run when a macro opens the file?

Yes. When code runs Workbooks.Open "Report.xlsm", that workbook's Workbook_Open fires normally. If you specifically need to suppress it — for example in an automated batch job — set Application.EnableEvents = False before the Workbooks.Open call, then set it back to True afterward.

How do I open a workbook without running Workbook_Open?

Hold the Shift key while the file opens to skip Workbook_Open for that one open. From code, set Application.EnableEvents = False before Workbooks.Open and restore it to True after. Both are useful when a startup macro is misbehaving and you need to get into the file to fix it.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-02.