VBA Worksheet_Activate and Deactivate in Excel — Run Code When You Switch Sheets (and Why You Can't Cancel a Leave)

TL;DR — Worksheet_Activate fires when a sheet becomes the active one (you land on it); Worksheet_Deactivate fires just as you leave it (before another sheet takes over). They're the arrival and departure events for a sheet — use Activate to refresh a dashboard on view, Deactivate to check things before the user moves on. The catch that defines them: neither has a Cancel argument. Unlike BeforeClose and BeforeSave, you can watch a sheet switch but you cannot block it — the best you can do is bounce the user back.

' In the sheet's own module (e.g. "Dashboard") — NOT ThisWorkbook, NOT a Module.
Private Sub Worksheet_Activate()
    Me.Range("A1").Select              ' land at the top every time you arrive
    ActiveWindow.ScrollRow = 1
    Me.Calculate                       ' refresh this sheet's figures on view
End Sub

Private Sub Worksheet_Deactivate()
    ' No Cancel parameter exists here — you can warn, but you can't stop the leave.
    If Me.Range("Signoff").Value = "" Then _
        MsgBox "Heads up: you left the Dashboard without signing off.", vbInformation
End Sub

Switching sheets is the most ordinary thing a user does, and these two events let a sheet react to it. Worksheet_Activate powers "refresh when I look at this tab" — recalc, requery, scroll to top, protect or unprotect. Worksheet_Deactivate powers "check before I walk away." The trick is understanding what these events can't do, because that's what separates them from the guard events.

What you'll learn

The mental model — arrival and departure events, one per sheet
Why there's no Cancel — and the bounce-back workaround when you need to hold a user
The refresh-on-view pattern (and the "didn't run on open" gotcha)
Sheet-level Worksheet_Activate vs workbook-level Workbook_SheetActivate
Why chart sheets and the EnableEvents guard both matter here

The mental model: arrival and departure gates

Think of each sheet as a gate at an airport. Worksheet_Activate fires the instant you walk into this gate — the sheet just became active. Worksheet_Deactivate fires as you walk out — you've clicked another tab, and this sheet is about to hand over. You never call either; Excel raises them on the switch, in order: the old sheet's Deactivate first, then the new sheet's Activate.

Both are parameterless, and both live in the specific sheet's code module — not ThisWorkbook, not a standard Module. Double-click the sheet (say, Dashboard) under "Microsoft Excel Objects" and write Private Sub Worksheet_Activate() there. Inside, Me refers to that sheet, which is why Me.Calculate and Me.Range(...) read cleanly.

The rule that defines these events: there is no veto

This is the insight that ties the whole cluster together. BeforeClose and BeforeSave each hand you a Cancel flag — your handler is a checkpoint that can stop the action. Worksheet_Activate and Worksheet_Deactivate have no such parameter. Look at the signatures: Worksheet_Deactivate() takes nothing. There is no Cancel to set, so there is no way to prevent the user leaving a sheet. By the time Deactivate runs, the decision to switch has already been made.

That's not an oversight — a sheet switch isn't a risky, irreversible act the way closing or saving is, so Excel doesn't offer a veto. But it means "don't let them leave this tab until B2 is filled" is not directly possible. The honest workaround is a bounce-back: detect the problem and re-activate the sheet, guarding against the re-trigger:

Private Sub Worksheet_Deactivate()
    If Me.Range("B2").Value = "" Then
        Application.EnableEvents = False    ' our Activate below must not cascade
        Me.Activate                         ' yank the user back to this sheet
        Application.EnableEvents = True
        MsgBox "Fill in B2 before leaving this sheet.", vbExclamation
    End If
End Sub

Understand what this is: not a cancelled switch, but a completed switch immediately followed by a switch back. It flickers, it's heavier than a real veto, and it re-fires events — hence the Application.EnableEvents guard, the same discipline used in Worksheet_Change. If you truly need to hold a user until data is valid, a UserForm shown modally is a cleaner tool than fighting the sheet events. Know the limit and you'll design around it instead of chasing a Cancel that doesn't exist.

The pattern that makes Activate worth it: refresh on view

The best use of Worksheet_Activate is "make this sheet correct the moment someone looks at it." A summary tab recalculates, a report requeries its source, a data-entry sheet scrolls to the top and unprotects the input cells:

Private Sub Worksheet_Activate()
    Me.Unprotect Password:="x"
    Me.Range("Inputs").Locked = False
    Me.Calculate
    Application.Goto Me.Range("A1"), Scroll:=True   ' put A1 top-left on arrival
End Sub

There's one gotcha that generates a steady stream of "my Activate didn't run" questions: the sheet that is already active when the workbook opens does not fire Worksheet_Activate — it was never switched to, it was simply the starting sheet. If you need the refresh to happen on that first sheet at open time, run it from Workbook_Open as well, or call your refresh routine from both.

The distinction to get right: sheet-level vs workbook-level

Worksheet_Activate in a sheet module fires only for that one sheet. If you want to react whenever the user lands on any sheet, don't paste the same handler into twelve sheet modules — use the workbook-level events in ThisWorkbook:

' In ThisWorkbook — fires for EVERY sheet, and tells you which one via Sh.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.StatusBar = "You are on: " & Sh.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' Sh is the sheet being left.
End Sub

The workbook versions hand you Sh — the sheet involved — so one handler covers the whole file. The rule: one specific sheet → Worksheet_Activate in that sheet; any sheet → Workbook_SheetActivate in ThisWorkbook. Choosing sheet-level when you meant workbook-level (or vice versa) is the most common reason "the event fires on the wrong tabs."

Two more edges worth knowing. Worksheet_Activate is for worksheets only — selecting a chart sheet raises Chart_Activate, not this event, so a handler that expects it will silently never run. And any writing or selecting you do inside these handlers can cascade into more events; keep them light and reach for Application.EnableEvents when they change the workbook.

How ExcelMaster helps

Sheet-switch events look simple but hide real judgment: which event (arrival vs departure), which level (sheet vs workbook), the fact that you can't cancel a leave, the first-sheet-at-open gotcha, and the EnableEvents guard on any bounce-back.

ExcelMaster lets you describe the outcome. Say "every time I open the Dashboard tab, recalculate it and scroll to the top," and it writes a Worksheet_Activate in the right sheet — and if you ask for "warn me if I leave without signing off," it adds a Worksheet_Deactivate that warns rather than pretending it can block, because it can't. You keep the workbook and the code; you skip learning the limits the hard way.

Frequently asked questions

What is the difference between Worksheet_Activate and Worksheet_Deactivate?

Worksheet_Activate runs when a sheet becomes the active one — you just switched to it. Worksheet_Deactivate runs as you leave that sheet, just before another becomes active. On a switch, Excel fires the old sheet's Deactivate first, then the new sheet's Activate. Both live in the specific sheet's code module and take no parameters.

Can I stop a user from leaving a worksheet in VBA?

Not directly — Worksheet_Deactivate has no Cancel parameter, so you cannot veto the switch the way Workbook_BeforeClose vetoes a close. The workaround is a bounce-back: in Deactivate, call Me.Activate to return the user to the sheet (guard it with Application.EnableEvents = False … = True), then show a message. For a true block, show a modal UserForm instead.

Why doesn't Worksheet_Activate run when the workbook opens?

Because the starting sheet was never switched to — it was already active when the file opened, so no activation event fires for it. Switching away and back will fire it, but for the initial sheet at open time, run your refresh from Workbook_Open as well.

How do I run code when any sheet is selected, not just one?

Use the workbook-level events in ThisWorkbook: Private Sub Workbook_SheetActivate(ByVal Sh As Object) fires for every sheet and passes Sh, the sheet that was activated. That's cleaner than copying a Worksheet_Activate into every sheet module.

Where does Worksheet_Activate code go?

In the code module of the specific worksheet — double-click that sheet (e.g. Sheet1 or Dashboard) under "Microsoft Excel Objects" in the Project Explorer and add Private Sub Worksheet_Activate(). It does not fire from a standard Module; the all-sheets version, Workbook_SheetActivate, goes in ThisWorkbook.

Tested in

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