VBA-Lesson 11.1. Workbook Events (Workbook Events)

We can have workbook events (eg opening, closing, etc.) that can be triggers (switches) for VBA code.

Workbook_Open

To follow the instructions when the workbook opens, go to ThisWorkbook and select Workbook:

The Workbook_Open event will be added by default and will act when the workbook is opened:

Private Sub Workbook_Open()

End Sub

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

Private Sub Workbook_Open()
      MsgBox "Welcome"
End Sub

Workbook_BeforeClose (Event before closing the workbook)

To execute the instructions before closing the book, select BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Closing the workbook can be canceled by setting True to the "Cancel" variable.

The following is an example in which the user is asked for confirmation to close the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
      'If the user answers NO, then the Cancel variable will have the value TRUE (which will cancel closing the workbook)
      If MsgBox("Do you really want to close this workbook ?", 36, "I confirm") = vbNo Then
          Cancel = True
      End If
End Sub

Workbook_BeforeSave (Event before saving the workbook)

This event occurs just before saving the workbook:

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

End Sub

The saving of the file can be canceled by setting True to the "Cancel" variable.

Workbook_BeforePrint (Event before printing the book)

This event occurs before the workbook is printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

The printing of the file can be canceled by setting True to the "Cancel" variable.

Workbook_AfterSave (Event after saving the workbook)

This event occurs immediately after saving the workbook:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

End Sub

Workbook_SheetActivate (Workbook Toggle Event)

This event occurs every time you switch between worksheets in a workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

In this example, the name of the activated sheet is displayed in the dialog box:

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

Workbook_SheetBeforeDoubleClick (Double-click event on a cell)

This event occurs when a worksheet cell is double-clicked:

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 fill color to a cell, depending on the selected 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

Workbook_SheetBeforeRightClick (Event before right click)

This event occurs before the right mouse click:

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

End Sub

Workbook_SheetChange (Sheet Content Change Event)

This event occurs whenever the content of the worksheet changes:

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

End Sub

Workbook_SheetCalculate (SheetCalculate Event)

This event occurs whenever data in a worksheet is calculated or recalculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Workbook_SheetSelectionChange (Selected cell range change event)

This event occurs whenever the content of the selected range of cells in the spreadsheet changes:

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

End Sub

In this example, the fill color changes 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 (Add new sheet event)

This event occurs whenever a new sheet is added to the workbook:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

Workbook_SheetFollowHyperlink (Link Click Event)

This event occurs when a link (hypertext) is clicked:

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

End Sub

Articles on the topic: