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