VBA Events
Event-Driven programming is a programming paradigm where interaction with a particular object triggers events which cause event procedures to run. Events can be triggered programmatically or by users interacting with a user interface. Events are most often used with UserForms, controls, and application-specific objects like Excel Workbooks and Worksheets. Users can also define custom events for their own custom classes.
WithEvents Keyword
The WithEvents keyword can be used in a class module to add events to member objects. To add event procedures to an event handling class module use the object box and procedure box drop-downs.
Event Handling Class
Creating an event handling class involves creating a class module and adding a member variable declared using the WithEvents keyword. The instance stored in the member variable will have events activated. Set the member variable to Nothing to remove event handling.
'Class Module: clsWSEvents
Option Explicit
Public WithEvents WS As Worksheet
Private Sub WS_Change(ByVal Target As Range)
Debug.Print "Worksheet Changed: " & WS.Name & ". Range: " & Target.Address & "."
End Sub
'Standard Module: Module1
Option Explicit
Public Sub Example()
Dim WSEvents As clsWSEvents
Set WSEvents = New clsWSEvents
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets(1)
'Add event handling
Set WSEvents.WS = WS
WS.Range("A1").Value = "Hello, World!" 'Event triggered
'Remove event handling
Set WSEvents.WS = Nothing
WS.Range("A1").Value = "Goodbye!" 'Event not triggered
End Sub
Event Handling Collection
A Collection object can be used to store multiple instances of an event handling class, each of which handles the events for a specific instance of a class. A class can be created to manage this Collection object and provide methods to add and remove objects.
'Class Module: clsWSEvents
Option Explicit
Public WithEvents WS As Worksheet
Private Sub WS_Change(ByVal Target As Range)
Debug.Print "Worksheet Changed: " & WS.Name & ". Range: " & Target.Address & "."
End Sub
'Class Module: clsWSEventsCollection
Option Explicit
Private WSEventsCollection As Collection
Private Sub Class_Initialize()
Set WSEventsCollection = New Collection
End Sub
Public Sub AddWorksheetEvents(WS As Worksheet, Key As String)
Dim WSEvents As clsWSEvents
Set WSEvents = New clsWSEvents
Set WSEvents.WS = WS
WSEventsCollection.Add WSEvents, Key
End Sub
Public Sub RemoveWorksheetEvents(Key As String)
WSEventsCollection.Remove Key
End Sub
Public Sub RemoveAllWorksheetEvents()
Set WSEventsCollection = New Collection
End Sub
'Standard Module: Module1
Option Explicit
Public Sub Example()
Dim WSEventsCollection As clsWSEventsCollection
Set WSEventsCollection = New clsWSEventsCollection
Dim WS As Worksheet
'Add Worksheet Events to All Worksheets in Workbook
For Each WS In ThisWorkbook.Worksheets
WSEventsCollection.AddWorksheetEvents WS, WS.Name
Next WS
'Trigger Events
For Each WS In ThisWorkbook.Worksheets
WS.Range("A1").Value = "Hello, World!"
Next WS
'Remove Worksheet Events from Worksheets
For Each WS In ThisWorkbook.Worksheets
WSEventsCollection.RemoveWorksheetEvents WS.Name
Next WS
'Events not triggered
For Each WS In ThisWorkbook.Worksheets
WS.Range("A1").Value = "Goodbye!"
Next WS
End Sub
Add Events on Workbook Open Event
To have events added when a Workbook opens, use the Workbook Open event procedure to instantiate the event handling class and store it in a Public variable in a standard module.
Note: The Workbook will need to be saved, closed, and re-opened to activate the events.
'Class Module: clsWBEvents
Option Explicit
Public WithEvents WB As Workbook
Private Sub WB_NewSheet(ByVal Sh As Object)
Debug.Print "Sheet Created: " & Sh.Name
End Sub
'Standard Module: Module1
Option Explicit
Public WBEvents As New clsWBEvents
Public Sub ActivateWorkbookEvents()
Set WBEvents.WB = ThisWorkbook
End Sub
Public Sub DeactivateWorkbookEvents()
Set WBEvents.WB = Nothing
End Sub
Public Sub AddWorksheet()
'Trigger Event
ThisWorkbook.Worksheets.Add
End Sub
'ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
ActivateWorkbookEvents
End Sub
User-Defined Events
To create and use user-defined events, declare an event in a class module using the Event statement, raise the event using the RaiseEvent statement, and add event handling code in another class module using the WithEvents keyword.
'Class Module: clsExample
Option Explicit
Public Event MessageSent(MessageText As String)
Private pMessage As String
Public Property Get Message() As String
Message = pMessage
End Property
Public Property Let Message(RHS As String)
pMessage = RHS
End Property
Public Sub SendMessage()
Debug.Print "Sending Message..."
RaiseEvent MessageSent(Me.Message)
End Sub
'Class Module: clsExampleEvents
Option Explicit
Public WithEvents Example As clsExample
Private Sub Example_MessageSent(MessageText As String)
Debug.Print "Message Sent: " & MessageText
End Sub
'Standard Module: Module1
Option Explicit
Public Sub Example()
Dim ExampleEvents As clsExampleEvents
Set ExampleEvents = New clsExampleEvents
Dim E As clsExample
Set E = New clsExample
Set ExampleEvents.Example = E
E.Message = "Hello, World!"
E.SendMessage
End Sub
UserForms
UserForms and their controls have associated event procedures that can be used to create interactive user interfaces. UserForms are discussed in more detail in the UserForms section.
ActiveX Controls
ActiveX controls can be added to Excel Worksheets and Word Documents. ActiveX controls are given event procedures in the code module for the Worksheet or Document object where the controls are located. ActiveX controls differ from Excel Form controls which can be assigned a macro but not events. To add event procedures to ActiveX controls either double-click on the control, right-click on the control and select View Code, or view the code window for the Worksheet or Document where the control is located through the project explorer. Use the object box and procedure box drop-downs to add event procedures.
Excel Events
Excel has event procedures available for Workbooks, Worksheets, Charts, QueryTables, and the Excel Application itself.
Enable and Disable Events
The EnableEvents property of the Application object can be set to True or False to enable or disable events in Excel. Events are enabled by default. Events can be triggered by a user interaction or by code. At times it may be necessary to disable events so a procedure can do a task without triggering events. Events should generally be re-enabled afterward so they can be triggered by user activity.
Public Sub Example()
Application.EnableEvents = False
'Code Here
Application.EnableEvents = True
End Sub
Workbook Events
Workbook Events are triggered by interactions with a Workbook object.
- Activate
- AddinInstall
- AddinUninstall
- AfterRemoteChange
- AfterSave
- AfterXmlExport
- AfterXmlImport
- BeforeClose
- BeforePrint
- BeforeRemoteChange
- BeforeSave
- BeforeXmlExport
- BeforeXmlImport
- Deactivate
- ModelChange
- NewChart
- NewSheet
- Open
- PivotTableCloseConnection
- PivotTableOpenConnection
- RowsetComplete
- SheetActivate
- SheetBeforeDelete
- SheetBeforeDoubleClick
- SheetBeforeRightClick
- SheetCalculate
- SheetChange
- SheetDeactivate
- SheetFollowHyperlink
- SheetLensGalleryRenderComplete
- SheetPivotTableAfterValueChange
- SheetPivotTableBeforeAllocateChanges
- SheetPivotTableBeforeCommitChanges
- SheetPivotTableBeforeDiscardChanges
- SheetPivotTableChangeSync
- SheetPivotTableUpdate
- SheetSelectionChange
- SheetTableUpdate
- Sync
- WindowActivate
- WindowDeactivate
- WindowResize
Workbook events can be accessed through the ThisWorkbook object in the VBA Project explorer. Event procedures can be inserted using the object and procedure drop-downs.
Worksheet Events
Worksheet Events are triggered through interactions with a Worksheet object.
- Activate
- BeforeDelete
- BeforeDoubleClick
- BeforeRightClick
- Calculate
- Change
- Deactivate
- FollowHyperlink
- LensGalleryRenderComplete
- PivotTableAfterValueChange
- PivotTableBeforeAllocateChanges
- PivotTableBeforeCommitChanges
- PivotTableBeforeDiscardChanges
- PivotTableChangeSync
- PivotTableUpdate
- SelectionChange
- TableUpdate
Worksheet events can be accessed through Worksheet objects in the VBA Project explorer. Event procedures can be inserted using the object and procedure drop-downs.
Chart Events
Chart Events are triggered through interactions with charts. Chart Events can be used with chart sheets and embedded charts.
- Activate
- BeforeDoubleClick
- BeforeRightClick
- Calculate
- Deactivate
- MouseDown
- MouseMove
- MouseUp
- Resize
- Select
- SeriesChange
Using Chart Events with chart sheets is very similar to using events with Worksheets. Both Worksheets and Charts are part of the Sheets collection. Insert a Chart using the F11 key and select View Code for the Chart in the VBA Project explorer.
Using Chart Events with Embedded Charts requires the use of an event handling class.
'Class Module: clsEmbeddedChartEvents
Option Explicit
Public WithEvents EmbeddedChart As Chart
Private Sub EmbeddedChart_Activate()
Debug.Print "Embedded Chart Activated: " & EmbeddedChart.Name
End Sub
'Standard Module: Module1
Option Explicit
Public EmbeddedChartWithEvents As New clsEmbeddedChartEvents
Public Sub ActivateEmbeddedChartEvents()
Dim C As Chart
Set C = ActiveSheet.ChartObjects(1).Chart
Set EmbeddedChartWithEvents.EmbeddedChart = C
End Sub
Public Sub DeactivateEmbeddedChartEvents()
Set EmbeddedChartWithEvents = Nothing
End Sub
'ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
ActivateEmbeddedChartEvents
End Sub
To add event procedures to an embedded chart use the procedure box drop-down in the event handling class module.
Application Events
Application events are triggered by interaction with the Excel application itself.
- AfterCalculate
- NewWorkbook
- ProtectedViewWindowActivate
- ProtectedViewWindowBeforeClose
- ProtectedViewWindowBeforeEdit
- ProtectedViewWindowDeactivate
- ProtectedViewWindowOpen
- ProtectedViewWindowResize
- SheetActivate
- SheetBeforeDelete
- SheetBeforeDoubleClick
- SheetBeforeRightClick
- SheetCalculate
- SheetChange
- SheetDeactivate
- SheetFollowHyperlink
- SheetLensGalleryRenderComplete
- SheetPivotTableAfterValueChange
- SheetPivotTableBeforeAllocateChanges
- SheetPivotTableBeforeCommitChanges
- SheetPivotTableBeforeDiscardChanges
- SheetPivotTableUpdate
- SheetSelectionChange
- SheetTableUpdate
- WindowActivate
- WindowDeactivate
- WindowResize
- WorkbookActivate
- WorkbookAddinInstall
- WorkbookAddinUninstall
- WorkbookAfterRemoteChange
- WorkbookAfterSave
- WorkbookAfterXmlExport
- WorkbookAfterXmlImport
- WorkbookBeforeClose
- WorkbookBeforePrint
- WorkbookBeforeRemoteChange
- WorkbookBeforeSave
- WorkbookBeforeXmlExport
- WorkbookBeforeXmlImport
- WorkbookDeactivate
- WorkbookModelChange
- WorkbookNewChart
- WorkbookNewSheet
- WorkbookOpen
- WorkbookPivotTableCloseConnection
- WorkbookPivotTableOpenConnection
- WorkbookRowsetComplete
- WorkbookSync
Using Application events requires creating an event handling class.
'Class Module: clsApplicationEvents
Option Explicit
Public WithEvents ExcelApp As Application
Private Sub ExcelApp_NewWorkbook(ByVal Wb As Workbook)
Debug.Print "Workbook Created"
End Sub
'Standard Module: Module1
Option Explicit
Public ApplicationEvents As New clsApplicationEvents
Public Sub ActivateApplicationEvents()
Set ApplicationEvents.ExcelApp = Excel.Application
End Sub
Public Sub DeactivateApplicationEvents()
Set ApplicationEvents = Nothing
End Sub
'ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
ActivateApplicationEvents
End Sub
To add event procedures use the object box and procedure box drop-downs in the event handling class module.
Query Table Events
QueryTable events are triggered by interactions with a QueryTable object.
- BeforeRefresh
- AfterRefresh
QueryTable events can be used by creating an event handling class. Instances of the event handling class can be added to a Collection to add events to more than one object.
'Class Module: clsQueryTableEvents
Option Explicit
Public WithEvents QT As QueryTable
Private Sub QT_BeforeRefresh(Cancel As Boolean)
Debug.Print "Before Refresh"
End Sub
'Standard Module: Module1
Option Explicit
Public QTEvents As New clsQueryTableEvents
Public Sub Example()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets(1)
Dim ConnectionString As String
ConnectionString = "TEXT;" & "C:\example.csv"
Dim QT As QueryTable
Set QT = WS.QueryTables.Add(ConnectionString, WS.Range("A1"))
With QT
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
'Add QueryTable Events
Set QTEvents.QT = QT
'Triggers Events
QT.Refresh
End Sub
To add event procedures use the object box and procedure box drop-downs in the event handling class module.