Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 37 ยท Forms, Events and Errors

VBA Events: Workbook, Worksheet and Application

Run VBA automatically with Excel events: Workbook_Open, BeforeSave, Worksheet_Change, SelectionChange, OnTime, and application-level events with WithEvents.

An event is something that happens in Excel, such as opening a workbook, changing a cell, saving a file or activating a sheet. An event procedure is a Sub with a special name that Excel calls for you when the event occurs. With events your code runs automatically instead of waiting for someone to click a button.

Events live in specific places: workbook events in the ThisWorkbook module, sheet events in the sheet's own module, and application-wide events in a class module. This lesson covers all three, plus OnTime scheduling, and the most important safety rule: protecting yourself from event loops.

VBA is read-only on this site. The runnable examples model event handling in JavaScript, including the recursion guard that every Worksheet_Change handler needs.

Workbook events (in ThisWorkbook)

VB
Private Sub Workbook_Open()
    Worksheets("Home").Activate
    Application.StatusBar = "Welcome, " & Application.UserName
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Worksheets("Info").Range("B2").Value = Now      ' stamp the save time
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Worksheets("Data").Range("A1").Value = "" Then
        If MsgBox("The data sheet is empty. Close anyway?", vbYesNo) = vbNo Then Cancel = True
    End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' fires for every sheet in this workbook
End Sub

Events with a Cancel argument let you stop the action by setting Cancel = True. The dropdowns at the top of the code window list all available events.

Worksheet events (in the sheet module)

VB
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub

    Application.EnableEvents = False          ' stop recursion
    On Error GoTo CleanUp
    Target.Value = UCase$(Target.Value)       ' this would trigger Change again!
CleanUp:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("F1").Value = "Row " & Target.Row
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 4 Then
        Target.Value = IIf(Target.Value = "Done", "", "Done")
        Cancel = True                         ' skip Excel's own edit mode
    End If
End Sub

Target is the changed range (it can be many cells after a paste). Intersect checks whether the change touched the cells you care about.

The recursion trap

If a Change handler itself changes a cell, Excel fires Change again, and the macro can loop until Excel crashes. Always:

  1. Exit early with Intersect when the change is not relevant.
  2. Set Application.EnableEvents = False before your own writes.
  3. Turn it back on in an error handler. If a macro crashes with events off, nothing in Excel reacts to anything until you run Application.EnableEvents = True in the Immediate window.

Application-level events

To react to events of any workbook, use a class module named clsApp and the WithEvents keyword.

VB
' Class module: clsApp
Public WithEvents App As Application

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Debug.Print "Saving " & Wb.Name
End Sub

' Standard module or ThisWorkbook
Dim watcher As clsApp

Private Sub Workbook_Open()
    Set watcher = New clsApp
    Set watcher.App = Application
End Sub

WithEvents only works with module-level variables inside class modules (and forms, sheets and ThisWorkbook). Keep the watcher object alive in a module-level variable, or it is garbage-collected and the events stop.

Scheduling with OnTime and keyboard shortcuts

VB
Private nextRun As Date

Sub StartTimer()
    nextRun = Now + TimeSerial(0, 0, 30)
    Application.OnTime nextRun, "RefreshData"
End Sub

Sub RefreshData()
    Worksheets("Data").Range("A1").Value = Now
    StartTimer                      ' schedule the next run
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime nextRun, "RefreshData", , False   ' cancel the schedule
End Sub

Sub AssignShortcut()
    Application.OnKey "^+R", "RefreshData"               ' Ctrl+Shift+R
End Sub

Always cancel OnTime in Workbook_BeforeClose, otherwise Excel may reopen the file to run the procedure.

Try it: events and the recursion guard

JS Change event with a recursion guard

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS A tiny event bus with Cancel

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Writing to cells in Worksheet_Change without disabling events, causing an endless loop.
  • Leaving EnableEvents False after an error. Put the restore in an error handler.
  • Putting an event procedure in a standard module: it must live in the correct object module.
  • Forgetting to cancel an OnTime schedule on close.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where does a Worksheet_Change procedure go?

    Sheet events must be in the code module of the sheet they belong to.
  2. 2. Why set Application.EnableEvents = False inside Worksheet_Change before writing to a cell?

    Writing a cell triggers Change again; disabling events avoids infinite recursion.
  3. 3. What does setting Cancel = True in Workbook_BeforeSave do?

    Events with a Cancel argument can stop the operation.
  4. 4. Which keyword lets a class module receive an Application's events?

    Declare the Application variable with WithEvents in a class module.

Summary

  • Events run code automatically: Workbook_Open, Workbook_BeforeSave, Worksheet_Change, SelectionChange and many more.
  • Put them in the right module (ThisWorkbook, a sheet module or a class module with WithEvents).
  • Use Intersect to filter, and EnableEvents = False plus an error handler to avoid loops.
  • Use Application.OnTime and OnKey for schedules and shortcuts, and cancel timers on close.

Next: Error Handling and Debugging