VBA Course: Workbook Events

We can have workbook events (for example, opening, closing, etc) trigger VBA code.

Workbook_Open

To execute instructions when the workbook is opened, go to ThisWorkbook and select Workbook:

The event Workbook_Open will be added by default, and takes effect when the workbook is opened:

Private Sub Workbook_Open()

End Sub

For example, if we add the following instruction, a dialog box will be displayed when the workbook is opened:

Private Sub Workbook_Open()
    MsgBox "Welcome"
End Sub

Workbook_BeforeClose

To execute instructions immediately before the workbook is closed, choose BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

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

Here is an example in which the user is asked to confirm the closing of the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'If the user responds NO, the Cancel variable will have the value TRUE (which will cancel the closing of the workbook)
    If MsgBox("Are you sure that you want to close this workbook ?", 36, "Confirm") = vbNo Then
        Cancel = True
    End If
End Sub

Workbook_BeforeSave

This event will fire immediately before the workbook is saved:

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

End Sub

File save can be cancelled by assigning the value True to the variable "Cancel".

Workbook_BeforePrint

This event will fire immediately before printing:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

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

Workbook_AfterSave

This event will fire immediately after a save command is completed:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

End Sub

Workbook_SheetActivate

This event will fire each time the user switches from one worksheet to another:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

In this example, the name of the worksheet is displayed in a dialog box:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox "Name of Sheet: " & Sh.Name
End Sub

Workbook_SheetBeforeDoubleClick

This event will fire immediately before a double click on a cell:

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

End Sub

For example, we can use this event to add a background color to a cell depending on which worksheet it is on:

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

Workbook_SheetBeforeRightClick

This event will fire immediately before a right click on a cell:

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

End Sub

Workbook_SheetChange

This event will fire each time the contents of a cell are modified:

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

End Sub

Workbook_SheetCalculate

This event will fire each time a worksheet's data is calculated or recalculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Workbook_SheetSelectionChange

This event will fire each time the selection changes on a calculation worksheet:

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

End Sub

In this example, a background color is added if cell 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) 'Blue color
    End If
End Sub

Workbook_NewSheet

This event will fire each time a new worksheet is added to the workbook:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

Workbook_SheetFollowHyperlink

This event will fire whenever a hypertext link is clicked:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

End Sub