An action performed by VBA code is called an event. Suppose, some events are executed automatically by Excel that’s because of the code written in VBA which is known as Event Handler. The execution of a specified macro can be triggered using an action which is known as an event. For example, if we want to change the color of a specific cell only by clicking on it or if we want to add a text-only by clicking on a button then we can use Events. There are two types of Events one is worksheet events and another one is workbook events which we will discuss in this article.
Worksheet Events
Step 1: Right-click on the sheet tab at the bottom of the Excel screen and select View Code.
Step 2: Select Sheet 1 from the Project-VBA Project tab.
Step 3: Select Worksheet from the drop-down box.
Step 4: Then we can select any events from the right-hand drop-down box.
Now, let’s discuss some useful worksheet events.
Activate Events
Syntax: expression.Activate
Expression: Which represents the Worksheet object
The following example is shown to sort the range B1:B8 in descending order when the worksheet is activated.
Deactivate Events
This event takes place when the worksheet is deactivated. The following example is shown to arrange the windows vertically when the worksheet is deactivated.
Change Event
This event is used to change the cell of the worksheet.
Syntax: expression.Change(Target)
Expression: Which represents the Worksheet object
Target: It is the range of the cell
The following example is shown to change the color of the cell to red.
PivotTableUpdate Event
This event is used to update the pivot table in the worksheet.
Syntax: expression.PivotTableUpdate (Target)
Expression: which represents the Worksheet object
Target: Selected Pivot table which we want to update
The following example is shown to update the private table displaying the message of the pivot table has been updated.
Workbook Events
For workbook events we have to follow some following steps:
Step 1: Double-click on ThisWorkbook of Project – VBAProject.
Step 2: Select Workbook from the drop-down box.
Step 3: Then we can select any events from the right-hand drop-down box.
Now, let’s discuss some useful workbook events
After Save Event
This event will take place after the workbook is saved.
Syntax: expression.AfterSave(Success)
Success: Returns true if changes have successfully taken otherwise returns false
The following code is to show the message after the workbook is saved.
Sheet Activate Event
This event takes place when the sheet is activated.
Syntax: expression.SheetActivate(sh)
The following code is to show the name of the activated sheet.
Sheet Change Event
This event takes place when the sheet is changed.
Syntax: expression.SheetChange(Sh , Target)
Sh:- The sheet object
Target: Range of the cell to be changed
New Sheet Event
This event takes place when a new sheet is created.
Syntax: expression.SheetActivate(sh)
sh: New sheet as an object
The following code is to move the new sheet to the end of the workbook.