Excel 2019

Application Sheet Events

In this tutorial you’ll learn how to capture events occurred on any sheet in Excel application. For example, the Workbook_SheetActivate event occurred when any sheet is activated in a particular workbook. If you want to listen the same event in every workbook available, you can use the application event which is triggered when any workbook is activated.

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

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:

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:

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:

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:

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:

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:

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

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:

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

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:

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:

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.

Advertisement:
Advertisement: