Events in Excel VBA help us to write event enabled programming. In this tutorial we are explaining basic of Event Programming and frequently used events with examples. You can download the example file and explore it.
In This Section:
- What Are Events?
- What Are The Different Types of Events?
- Practical Learning: Workbook_Open Event
- Example File with Workbook Events, Worksheet Events and Form Events
What Are Events?
Event programming is the most useful tool which helps to monitor specific user actions within Excel. For example, if the user Opening a Workbook, Selects a Worksheet, enters data into a Cell, or saves a Workbook, these actions are all Excel Events.
What Are The Different Types of Events?
Events are linked to Excel Worksheets, Charts, Workbooks, UserForms, or to the Excel Application itself. We can mainly classify these events as following:
Events that linked to a particular workbook.
For examples, Open (when the workbook is opened or created) , BeforeSave (when the workbook is about to be saved),etc…
Events that linked to a particular worksheet.
For examples, Change (when a Cell on the sheet is changed), SelectionChange (when the user moves selection from one the cell to another), etc…
Events that linked to a particular chart.
For examples, Select (when an object in the chart is selected), SeriesChange (when a value of a data point in a series is changed). etc…
Events that linked to the application (Excel).
For examples, WorkbookBeforeClose (when any workbook is about to be closed), and SheetChange (a cell in any open workbook is altered).
UserForm and ActiveX Contorls Events:
Events that linked to a particular UserForm or an object contained on
For example, Initialize (before an userform is displayed), Click (when an ActiveX control is clicked), etc…
Events in Excel VBA – Practical Learning: Workbook_Open Event
If you want to execute a macro on opening your Excel File, you can write the code for Workbook_Open event. Follow the below steps to do this:
Step 1: Open your Workbook and Open the VBE Editor by pressing Alt+F11
Step 2: Double Click On “ThisWorkbook” module -> Select “Workbook” From the left side drop-down list -> Select “Open” from the right side drop-down list as shown below:
You will see the the code for open event as follows:
Private Sub Workbook_Open() End Sub
Step 3: You can write the statements which you wants to execute while opening the workbook in this event procedure:
Private Sub Workbook_Open() 'write the statements here to execute while opening this workbook End Sub
For example if you wants to show a message box while opening the workbook, your code will be something like this:
Private Sub Workbook_Open() MsgBox "Workbook Event: Auto Run or Workbook_Open (You can run any procedure automatically while opening the workbook" End Sub
Step 4: You are done!Yes- Save your workbook as a macro enabled file (i.e; .xlsm), Close and Open to check it
A message box should appear while opening your workbook (after enabling the macros) as shown below:
Example File with Workbook Events, Worksheet Events and Form Events
Download the below file and see the several examples on Workbook Events, Worksheet Events and Form Events
Download Now: Events