Events in Excel VBA

Share: Facebook | Twitter | Whatsapp | Linkedin Visits: 1014

---

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:

Workbook Events:

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…

Worksheet Events:

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…

Chart Events:

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…

Application Events:

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
the UserForm.
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()
***code
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: