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.

Worksheet Event Handling Class Procedure Box

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.

UserForm Events UserForm Command Button UserForm Command Button Click

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.

Insert ActiveX Controls
ActiveX Control View Code
ActiveX Control Procedure Box

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.

Workbook Event Procedures
  1. Activate
  2. AddinInstall
  3. AddinUninstall
  4. AfterRemoteChange
  5. AfterSave
  6. AfterXmlExport
  7. AfterXmlImport
  8. BeforeClose
  9. BeforePrint
  10. BeforeRemoteChange
  11. BeforeSave
  12. BeforeXmlExport
  13. BeforeXmlImport
  14. Deactivate
  15. ModelChange
  16. NewChart
  17. NewSheet
  18. Open
  19. PivotTableCloseConnection
  20. PivotTableOpenConnection
  21. RowsetComplete
  22. SheetActivate
  23. SheetBeforeDelete
  24. SheetBeforeDoubleClick
  25. SheetBeforeRightClick
  26. SheetCalculate
  27. SheetChange
  28. SheetDeactivate
  29. SheetFollowHyperlink
  30. SheetLensGalleryRenderComplete
  31. SheetPivotTableAfterValueChange
  32. SheetPivotTableBeforeAllocateChanges
  33. SheetPivotTableBeforeCommitChanges
  34. SheetPivotTableBeforeDiscardChanges
  35. SheetPivotTableChangeSync
  36. SheetPivotTableUpdate
  37. SheetSelectionChange
  38. SheetTableUpdate
  39. Sync
  40. WindowActivate
  41. WindowDeactivate
  42. 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.

Project Explorer ThisWorkbook Workbook Procedure Box

Worksheet Events

Worksheet Events are triggered through interactions with a Worksheet object.

Worksheet Event Procedures
  1. Activate
  2. BeforeDelete
  3. BeforeDoubleClick
  4. BeforeRightClick
  5. Calculate
  6. Change
  7. Deactivate
  8. FollowHyperlink
  9. LensGalleryRenderComplete
  10. PivotTableAfterValueChange
  11. PivotTableBeforeAllocateChanges
  12. PivotTableBeforeCommitChanges
  13. PivotTableBeforeDiscardChanges
  14. PivotTableChangeSync
  15. PivotTableUpdate
  16. SelectionChange
  17. 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.

Worksheet View Code Worksheet Procedure Box

Chart Events

Chart Events are triggered through interactions with charts. Chart Events can be used with chart sheets and embedded charts.

Chart Event Procedures
  1. Activate
  2. BeforeDoubleClick
  3. BeforeRightClick
  4. Calculate
  5. Deactivate
  6. MouseDown
  7. MouseMove
  8. MouseUp
  9. Resize
  10. Select
  11. 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.

Chart View Code Chart Procedure Box

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.

Embedded Chart Procedure Box

Application Events

Application events are triggered by interaction with the Excel application itself.

Application Event Procedures
  1. AfterCalculate
  2. NewWorkbook
  3. ProtectedViewWindowActivate
  4. ProtectedViewWindowBeforeClose
  5. ProtectedViewWindowBeforeEdit
  6. ProtectedViewWindowDeactivate
  7. ProtectedViewWindowOpen
  8. ProtectedViewWindowResize
  9. SheetActivate
  10. SheetBeforeDelete
  11. SheetBeforeDoubleClick
  12. SheetBeforeRightClick
  13. SheetCalculate
  14. SheetChange
  15. SheetDeactivate
  16. SheetFollowHyperlink
  17. SheetLensGalleryRenderComplete
  18. SheetPivotTableAfterValueChange
  19. SheetPivotTableBeforeAllocateChanges
  20. SheetPivotTableBeforeCommitChanges
  21. SheetPivotTableBeforeDiscardChanges
  22. SheetPivotTableUpdate
  23. SheetSelectionChange
  24. SheetTableUpdate
  25. WindowActivate
  26. WindowDeactivate
  27. WindowResize
  28. WorkbookActivate
  29. WorkbookAddinInstall
  30. WorkbookAddinUninstall
  31. WorkbookAfterRemoteChange
  32. WorkbookAfterSave
  33. WorkbookAfterXmlExport
  34. WorkbookAfterXmlImport
  35. WorkbookBeforeClose
  36. WorkbookBeforePrint
  37. WorkbookBeforeRemoteChange
  38. WorkbookBeforeSave
  39. WorkbookBeforeXmlExport
  40. WorkbookBeforeXmlImport
  41. WorkbookDeactivate
  42. WorkbookModelChange
  43. WorkbookNewChart
  44. WorkbookNewSheet
  45. WorkbookOpen
  46. WorkbookPivotTableCloseConnection
  47. WorkbookPivotTableOpenConnection
  48. WorkbookRowsetComplete
  49. 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.

Application Events Procedure Box

Query Table Events

QueryTable events are triggered by interactions with a QueryTable object.

Query Table Event Procedures
  1. BeforeRefresh
  2. 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.

QueryTable Events Procedure Box