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:

workbook events

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

CopyPrivate Sub Workbook_Open()

End Sub

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

CopyPrivate Sub Workbook_Open()
    MsgBox "Welcome"
End Sub

Workbook_BeforeClose

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

close workbook events
CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

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

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Workbook_SheetSelectionChange

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

CopyPrivate 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:

CopyPrivate 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:

CopyPrivate Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

Workbook_SheetFollowHyperlink

This event will fire whenever a hypertext link is clicked:

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

End Sub
Download PDF files of this course