Excel VBA Events

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.