Workbook Sheet Events

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:

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.

Workbook_SheetBeforeRightClick

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.

Workbook_SheetFollowHyperlink

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.