Excel 2019

Event Procedures

Excel fires events in response of user actions. Events are procedures that automatically trigger when something happens to the Excel application. For example, Workbook_BeforeClose event fires when closing the workbook, Chart_Activate event occurs when a chart is activated and Worksheet_Change event fires when cells on the worksheet are changed.

Excel is made up of objects, such as workbooks, worksheets, cells, charts, pivot tables, and even the entire Excel application is an object. You can write a VBA code snippet for a specific object that will execute when something happened to that object. Excel can monitor several events that can be classified by their object types:

  • Application events
    Events that occur for the Excel itself. For example: NewWorkbook event (a new workbook created)

  • Chart events
    Events that occur for a specific chart. For example: Select event (something in the chart is selected)

  • UserForm events
    Events that occur for a specific UserForm. For example: Click event (a button CommandButtonis clicked).

  • Workbook events
    Events that occur for a specific workbook. For example: Open event (the workbook is opened)

  • Worksheet events
    Events that occur for a specific worksheet. For example: Change event (a cell value changed on the sheet)

  • Other events (OnTime and OnKey events)
    Application-level events that are not associated with any Excel object

How and where to write the event procedures

In the Visual Basic Editor (VBE) window (see How to open the VBE), every workbook has a project listed in the Project window as shown in following figure:

VBE Project Window

Each of the following objects has its own code module. Double click on a module to open the code window for writing the code:

  • Microsoft Excel Objects

    • Sheet objects, for example, Sheet1, Sheet2, Sheet3 and so on
      Each worksheet comes with its own built-in module, so you never need to create a module for any worksheet. Use these modules for event-handler code related to the particular worksheet.

    • ThisWorkbook object
      Use this module for event-handler code related to the workbook. Each Excel workbook comes with ThisWorkbook module for workbook related coding.

    • Chart objects, for example Chart1
      Each chart sheet (not embeded charts) comes with its own built-in module. Use this module for event-handler code related to the particular chart.

  • Modules
    Modules are containers for the macros that the Macro Recorder (or you) creates. Never put event-handler procedures in a module.

  • Forms, for example UserForm1
    Use this module for event-handler code related to the UserForm.

  • Class Modules
    Use these modules for event-handler code related to the embedded charts events and application level events.

Advertisement:
Advertisement: