4. VBA: Workbook and Worksheet Events

An event is a procedure that Excel calls by itself: when a workbook opens, a cell changes, or before saving. Its name and location are strictly defined. The easiest way to create one is through the drop-downs above the code window: the object on the left (Workbook or Worksheet), the event on the right.

• the ThisWorkbook module: Workbook_Open, Workbook_BeforeSave, Workbook_BeforeClose;
• a worksheet module: Worksheet_Change(ByVal Target As Range) fires when the user changes cells, and Target is which cells; Worksheet_SelectionChange fires when the selection moves, and Worksheet_Calculate fires when the sheet recalculates.

' module of the "Requests" sheet
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2:C500")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now        ' D: when the status was changed
    Application.EnableEvents = True
End Sub

' ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Worksheets("Requests").Range("B2").Value = "" Then
        MsgBox "Enter the owner in B2"
        Cancel = True
    End If
End Sub

How it works: Intersect returns the overlap of ranges. If the changed cell is outside C2:C500, there is no overlap (Is Nothing) and we exit. Writing the date into D is itself a change to the sheet, and without EnableEvents = False the event would trigger itself again. Cancel = True in BeforeSave cancels the save.

Note: Worksheet_Change doesn't fire when formulas recalculate. For that there is Worksheet_Calculate. Events work only if macros are enabled in the workbook. Changes made by a macro can't be undone with Ctrl+Z.

The simulator doesn't run macros or scripts. Run the code from the lesson in desktop Excel (VBA) or in Excel for the web (Office Scripts). Here you learn to read code and check yourself with formulas and a quiz.