VBA Course: Workbook Events

Events

So far, we have launched our macros by clicking on a button.

It is also possible to automatically execute them during a specific event of the workbook, such as the opening of the workbook, its closing, saving, etc.

Workbook_Open (on opening)

To execute instructions upon the opening of the workbook (after the user enables macros), go to ThisWorkbook and select Workbook:

The Workbook_Open event is added by default, and it acts upon the opening of the workbook:

Private Sub Workbook_Open()

End Sub

For example, by adding the following instruction, a dialog box will be displayed upon the opening of the workbook:

Private Sub Workbook_Open()

    MsgBox "Welcome message"

End Sub

Workbook_BeforeClose (before closing)

To execute instructions just before closing the workbook, choose BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

The closing of the workbook can be canceled by assigning the value True to the variable Cancel.

Here is an example where the user must confirm the closure of the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'If the user responds No, the Cancel variable will be set to True (which cancels the closure)
    If MsgBox("Are you sure you want to close this workbook?", 36, "Confirmation") = vbNo Then
        Cancel = True
    End If

End Sub

Workbook_BeforeSave (before saving)

This event occurs just before saving:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Saving can be canceled by assigning the value True to the variable Cancel.

Workbook_AfterSave (after saving)

This event occurs just after saving:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

End Sub

Workbook_BeforePrint (before printing)

This event occurs just before printing:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Printing can be canceled by assigning the value True to the variable Cancel.

Workbook_SheetActivate (on sheet activation)

This event occurs with each sheet change:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

For example, displaying the sheet name in a dialog box:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    MsgBox "Sheet name: " & Sh.Name

End Sub

Workbook_SheetBeforeDoubleClick (before double-click)

This event occurs just before double-clicking a cell:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub

For example, coloring a double-clicked cell based on the sheet:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    If Sh.Name = "Sheet1" Then
        Target.Interior.Color = RGB(255, 108, 0) 'Orange color
    Else
        Target.Interior.Color = RGB(136, 255, 0) 'Green color
    End If

End Sub

The double-click can be canceled by assigning the value True to the variable Cancel.

Workbook_SheetBeforeRightClick (before right-click)

This event occurs just before right-clicking a cell:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub

Workbook_SheetChange (on cell modification)

This event occurs with every modification of a cell's content:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Workbook_SheetSelectionChange (on selection change)

This event occurs with each change of selection on a worksheet:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

For example, highlighting the selection if A1 is empty:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Range("A1") = "" Then
        Target.Interior.Color = RGB(124, 255, 255) 'Light blue color
    End If

End Sub

Workbook_NewSheet (on sheet insertion)

This event occurs with each insertion of a new sheet:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub