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:
Events that occur for the Excel itself. For example:
NewWorkbookevent (a new workbook created)
Events that occur for a specific chart. For example:
Selectevent (something in the chart is selected)
Events that occur for a specific UserForm. For example:
Clickevent (a button
Events that occur for a specific workbook. For example:
Openevent (the workbook is opened)
Events that occur for a specific worksheet. For example:
Changeevent (a cell value changed on the sheet)
Other 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:
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,
Sheet3and 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.
Use this module for event-handler code related to the workbook. Each Excel workbook comes with
ThisWorkbookmodule for workbook related coding.
Chart objects, for example
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 are containers for the macros that the Macro Recorder (or you) creates. Never put event-handler procedures in a module.
Forms, for example
Use this module for event-handler code related to the
Use these modules for event-handler code related to the embedded charts events and application level events.