Application Sheet Events

To program application events you need to create an object variable that represents the Excel’s Application object and propagate its events, see How to Listen Application Events. To write event listener procedures you need to open VBE, press Alt+F11 to open the VBE and write the following code in the ThisWorkbook code module to create the Application object variable:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub

Activating application object events:

In this tutorial we’ll discuss the following events for application object:

• SheetActivate
• SheetBeforeDelete
• SheetBeforeDoubleClick
• SheetBeforeRightClick
• SheetCalculate
• SheetChange
• SheetDeactivate
• SheetFollowHyperlink
• SheetLensGalleryRenderComplete
• SheetSelectionChange
• SheetTableUpdate

SheetActivate

Syntax: App_SheetActivate(Sh)

The SheetActivate event occurs when any sheet is activated in the Excel application. The procedure uses one argument Sh, which represents the sheet that was activated.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetActivate
  Executes whenever any worksheet is activated in a particular workbook.
• Worksheet_Activate
  Executes whenever a particular worksheet is activated.

App_SheetActivate Example:

The Sh argument declares as an Object data type rather than a Worksheet data type. Because a sheet object either a Worksheet or Chart sheet. You can use the TypeName function to determine the type of Sh object.

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)
 MsgBox "Sheet type: " & TypeName(Sh)
End Sub

SheetBeforeDelete

Syntax: App_SheetBeforeDelete(Sh)

The SheetBeforeDelete event occurs before a sheet is deleted in the Excel application. The event listener procedure uses one argument Sh, which represents the sheet to be deleted. The procedure does not have a Cancel argument, so you can not cancel the delete operation.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetBeforeDelete
  Executes before any worksheet is deleted in a particular workbook.
• Worksheet_BeforeDelete
  Executes when a particular worksheet is about to be deleted.

App_SheetBeforeDelete Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

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

SheetBeforeDoubleClick

Syntax: App_SheetBeforeDoubleClick(Sh, Target, Cancel)

The SheetBeforeDoubleClick event occurs when a cell on any worksheet is about to be double-clicked. The event listener 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.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetBeforeDoubleClick
  Executes before a cell is about to be double-clicked on any worksheet in a particular workbook.
• Worksheet_BeforeDoubleClick
  Executes when a particular worksheet is about to be double-clicked.

App_SheetBeforeDoubleClick Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_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

SheetBeforeRightClick

Syntax: App_SheetBeforeRightClick(Sh, Target, Cancel)

The SheetBeforeRightClick occurs when the user right-clicks any worksheet in the Excel application. The event listener 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.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetBeforeRightClick
  Executes before user right clicked on any worksheet in a particular workbook.
• Worksheet_BeforeRightClick
  Executes when a particular worksheet is about to be right clicked.

App_SheetBeforeRightClick Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
 If Not Sh.Parent.Name = "BrainBellApp.xlsm" Then
  Cancel = True
  MsgBox "Right-click disabled"
 End If
End Sub

SheetCalculate

Syntax: App_SheetCalculate(Sh)

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

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetCalculate
  Executes when any worksheet is calculated in a particular workbook.
• Worksheet_Calculate
  Executes when a particular worksheet is calculated.

App_SheetCalculate Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

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

SheetChange

Syntax: App_SheetChange(Sh, Target)

The SheetChange event occurs when the user changes any cell’s content on any worksheet in the Excel application. The event listener procedure has two arguments, Sh which represent the sheet that triggers the change and Target which represents the range that was changed.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetChange
  Executes when a user changes any cell’s content on any worksheet in the active workbook.
• Worksheet_Change
  Executes when a user changes any cell’s content on a particular worksheet.

App_SheetChange Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 MsgBox Target.Address _
  & " changed on sheet " & Sh.Name _
   & ", workbook " & Sh.Parent.Name
End Sub

SheetDeactivate

Syntax: App_SheetDeactivate(Sh)

The SheetDeactivate event occurs when any sheet in any workbook is deactivated, such as when a different sheet in any workbook is activated. The event listener procedure uses one argument Sh that represents the sheet object that is deactivated.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetDeactivate
  Executes when any sheet in the particular workbook is deactivated.
• Worksheet_Deactivate
  Executes when a user leave (deactivate) a particular worksheet.

App_SheetDeactivate Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetDeactivate(ByVal Sh As Object)
 MsgBox Sh.Name & " deactived on workbook " _
  & Sh.Parent.Name
End Sub

SheetFollowHyperlink

Syntax: App_SheetFollowHyperlink(Sh, Target)

The SheetFollowHyperlink event occurs when you click any hyperlink in Excel application. This event has two arguments, Sh is the sheet object and Target is the hyperlink.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetFollowHyperlink
  Executes when a hyperlink in any sheet in a particular workbook is clicked.
• Worksheet_FollowHyperlink
  Executes when a user clicked a hyperlink in a particular worksheet.

App_SheetFollowHyperlink Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
 MsgBox "You clicked " & Target.Address _
 & " on workbook " & Sh.Parent.Name
End Sub

SheetLensGalleryRenderComplete

Syntax: App_SheetLensGalleryRenderComplete(Sh)

The SheetLensGalleryRenderComplete event occurs when the user selects the Quick Analysis tool on Excel application. The event listener procedure has one argument Sh which represents the active worksheet.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetLensGalleryRenderComplete
  Executes when a user selects the Quick Analysis tool in any sheet in a particular workbook is clicked.
• Worksheet_LensGalleryRenderComplete
  Executes when a user selects the Quick Analysis tool in a particular worksheet.

App_SheetLensGalleryRenderComplete Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetLensGalleryRenderComplete(ByVal Sh As Object)
 MsgBox "Quick Analsis tool selected on " _
  & Sh.Parent.Name
End Sub

SheetSelectionChange

Syntax: App_SheetSelectionChange(Sh, Target)

The SheetSelectionChange event occurs when a different cell is selected on any worksheet in the Excel. The event listener procedure has two arguments, Sh represents the sheet object and Target represents the new selected range.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetSelectionChange
  Executes when a different cell is selected on any sheet in a particular workbook.
• Worksheet_SelectionChange
  Executes when a different cell is selected on a particular worksheet.

App_SheetSelectionChange Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

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

SheetTableUpdate

Syntax: App_SheetTableUpdate(Sh, Target)

The SheetTableUpdate event occurs after a query table connected to a data model is updated on any workbook in Excel. This procedure has two arguments: Sh argument is the sheet containing query table and Target argument is the query table that was updated.

This event occurs for any sheet in any opened workbook, use following event-procedures for a particular workbook or a particular worksheet:

• Workbook_SheetTableUpdate
  Executes after a query table connected to a data model is updated on any sheet in a particular workbook.
• Worksheet_TableUpdate
  Executes after a query table connected to a data model is updated on a particular worksheet.

For more information, see how to create a query table / data model.

App_SheetTableUpdate Example:

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

Private Sub App_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)
 MsgBox Target.ListObject.Name & " updated on sheet " _
  & Sh.Name & ", workbook: " & Sh.Parent.Name
End Sub

The above code executed when a query table get updates on any sheet in Excel.