Excel 2019

Workbook Sheet Events

In this tutorial you'll learn how to capture events occur for any sheet in the active workbook. The event procedures for these events use at least one argument (Sh) which represents the effected sheet. Excel declares the Sh argument as an Object data type rather than a Worksheet data type. Because a workbook has several types of Sheet objects such as: Worksheet and Chart (also Dialog and Macro which are obsolete). You can use the TypeName function to determine the type of Sh object. For example, TypeName(Sh) function returns Worksheet if the sheet is a worksheet or returns Chart if it is a chart sheet.

Open a workbook, press Alt + F11 to open VBE, click ThisWorkbook in the Project window and then choose Workbook from the Object drop-down, the next drop-down displays a list of all workbook events, see following figure:
Workbook sheet events

  1. SheetActivate
  2. SheetDeactivate
  3. SheetBeforeDelete
  4. SheetBeforeDoubleClick
  5. SheetBeforeRightClick
  6. SheetCalculate
  7. SheetChange
  8. SheetSelectionChange
  9. SheetFollowHyperlink
  10. SheetLensGalleryRenderComplete
  11. SheetTableUpdate

Workbook_SheetActivate

Syntax: Workbook_SheetActivate(Sh)

The SheetActivate event occurs when a sheet is activated in the workbook. To listen this event Workbook_SheetActivate procedure is used. This procedure uses one argument Sh, which represents the sheet that was activated.

Workbook_SheetActivate Examples:

The following code is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 MsgBox Sh.Name
End Sub

The Sh argument is declared as an Object data type rather than a Worksheet data type because Excel has several types of Sheet objects. You can use the TypeName function to determine the sheet type. The following example displays a message with the type of the activated sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 MsgBox TypeName(Sh)
End Sub

The Workbook_SheetActivate event occurs for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_Activate event procedure.

Workbook_SheetDeactivate

Syntax: Workbook_SheetDeactivate(Sh)

The SheetDeactivate event occurs when any sheet in the workbook is deactivated, such as when a different sheet in the workbook is activated. To listen this event Workbook_SheetDeactivate procedure is used. This procedure uses one argument Sh that stores the sheet object that is deactivated.

Workbook_SheetDeactivate Example:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
 If Sh.Name = "Sheet1" Then
  MsgBox Sh.Name & " was deactivated"
 End If
End Sub

The Workbook_SheetDeactivate procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_Deactivate event procedure.

Workbook_SheetBeforeDelete

Syntax: Workbook_SheetBeforeDelete(Sh)

The SheetBeforeDelete event occurs before a sheet is deleted in the workbook. To listen this event Workbook_SheetBeforeDelete procedure is used. This procedure uses one argument Sh, which represents the sheet to be deleted. The Workbook_SheetBeforeDelete procedure does not have a Cancel argument, so you can not cancel the delete operation.

Workbook_SheetBeforeDelete Example:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
 MsgBox "Deleting " & Sh.Name
End Sub

The Workbook_SheetBeforeDelete procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_BeforeDelete event procedure.

Workbook_SheetBeforeDoubleClick

Syntax: Workbook_SheetBeforeDoubleClick(Sh, Target, Cancel)

The SheetBeforeDoubleClick event occurs when a cell on any worksheet is about to be double-clicked. To listen this event Workbook_SheetBeforeDoubleClick procedure is used. This procedure uses three arguments:

  1. Sh
    Represent the sheet
  2. Target
    It is the Range object which represents the cell that was double-clicked.
  3. Cancel
    By default, double-clicking a cell puts it into edit mode. You can halt this default behavior by assigning the True value to the Cancel argument.

Workbook_SheetBeforeDoubleClick Example:

The following code writes BrainBell.com on the double-clicked cell if the cell is empty and clear the cell if it is not empty:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
 Cancel = True
 If IsEmpty(Target) = True Then
  Target.Value = "BrainBell.com"
 Else
  Target.Clear
 End If
End Sub

The Workbook_SheetBeforeDoubleClick procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_BeforeDoubleClick event procedure.

Syntax: Workbook_SheetBeforeRightClick(Sh, Target, Cancel)

The SheetBeforeRightClick occurs when the user right-clicks any worksheet in the active workbook. To listen this event Workbook_SheetBeforeRightClick procedure is used. This procedure has two arguments:

  1. Sh
    Represents the active sheet
  2. Cancel
    You can cancel the right-click behavior by assigning the True value to the Cancel argument.

Workbook_SheetBeforeRightClick Example:

The following code disables the right-click effect on Sheet1 of active workbook:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
 If Sh.Name = "Sheet1" Then
  Cancel = True
  MsgBox "Right-click is disabled for Sheet1"
 End If
End Sub

The Workbook_SheetBeforeRightClick procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_BeforeRightClick event procedure.

Workbook_SheetCalculate

Syntax: Workbook_SheetCalculate(Sh)

The SheetCalculate event occurs when any worksheet is recalculated or after any changed data is plotted on a chart. To listen this event Workbook_SheetCalculate procedure is used. This procedure has one argument Sh which represent the sheet that triggers the calculation.

Workbook_SheetCalculate(Sh) Example:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
 MsgBox Sh.Name & " was recalculated"
End Sub

The Workbook_SheetCalculate procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_Calculate event procedure.

Workbook_SheetChange

Syntax: Workbook_SheetChange(Sh, Target)

The SheetChange event occurs when the user changes any cell’s content on any worksheet in the active workbook. To listen this event Workbook_SheetChange procedure is used. This procedure has two arguments, Sh which represent the sheet that triggers the change and Target which represents the range that was changed.

Workbook_SheetChange Example:

Following code displays a message with the address of changed cell/range:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Not Sh.Name = "Sheet1" Then Exit Sub
 MsgBox Target.Address & " has changed"
End Sub

The Workbook_SheetChange procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_Change event procedure.

Workbook_SheetSelectionChange

The SheetSelectionChange event occurs when a different cell is selected on any worksheet in the workbook. To listen this event the Workbook_SheetSelectionChange procedure is used. This procedure has two arguments, Sh represents the active sheet and Target represents the new selected range.

Workbook_SheetSelectionChange Example:

The following procedure highlights the each new cell selection:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 Target.Interior.ColorIndex = 4
End Sub

The Workbook_SheetSelectionChange procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_SelectionChange event procedure.

Syntax: Workbook_SheetFollowHyperlink(Sh, Target)

The SheetFollowHyperlink event occurs when you click any hyperlink in active workbook. To listen this event the Workbook_SheetFollowHyperlink procedure is used. This event has two arguments, Sh is the active worksheet and Target is the hyperlink.

Workbook_SheetFollowHyperlink Example:

Following code displays a message when you click on a link:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
 MsgBox Target.Address
End Sub

The Workbook_SheetFollowHyperlink procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_FollowHyperlink event procedure.

Workbook_SheetLensGalleryRenderComplete

Syntax: Workbook_SheetLensGalleryRenderComplete(Sh)

The SheetLensGalleryRenderComplete event occurs when the user selects the Quick Analysis tool. To listen this event the Workbook_SheetLensGalleryRenderComplete procedure is used. This procedure has one argument Sh which represents the active worksheet.

Workbook_SheetLensGalleryRenderComplete Example:

Private Sub Workbook_SheetLensGalleryRenderComplete(ByVal Sh As Object)
 MsgBox "you've selected the Quick Analysis tool on " & Sh.Name
End Sub

The Workbook_SheetLensGalleryRenderComplete procedure executes for all worksheets in the workbook, if you want to capture the event for a specific worksheet, use Worksheet_LensGalleryRenderComplete event procedure.

Workbook_SheetTableUpdate

Syntax: Workbook_SheetTableUpdate(Sh, Target)

The SheetTableUpdate event occurs after a query table connected to a data model is updated. To listen this event Workbook_SheetTableUpdate procedure is used. This procedure has two arguments: Sh argument is the sheet with the query table and Target argument is the query table that was updated.

Workbook_SheetTableUpdate Example:

Following code displays a message with the table and sheet names when a query table gets update:

Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)
 MsgBox Target.ListObject.Name & " updated on " & Sh.Name
End Sub

For more information, learn how to create a query table / data model. The above code is executed when a query table updated on any sheet in the workbook. To listen this event for a specific worksheet, use Worksheet_TableUpdate procedure in the code module for that worksheet.

Advertisement:
Advertisement: