Open In App

Excel VBA Events

Last Updated : 04 Dec, 2022
Comments
Improve
Suggest changes
Like Article
Like
Report

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.

Selecting-view-code
 

Step 2: Select Sheet 1 from the Project-VBA Project tab.

Selecting-sheet1
 

Step 3: Select Worksheet from the drop-down box. 

Selecting-worksheet
 

Step 4: Then we can select any events from the right-hand drop-down box.

Selecting-events
 

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.

Worksheet-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.

Worksheet-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.

Changing-color-of-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.

pivot-table-updated
 

Workbook Events

For workbook events we have to follow some following steps:

Step 1: Double-click on ThisWorkbook of Project - VBAProject.

Double-clicking-on-thisworkbook
 

Step 2: Select Workbook from the drop-down box.

Selecting-workbook
 

Step 3: Then we can select any events from the right-hand drop-down box.

Selecting-event
 

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.

Workbook-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.

Name-of-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

Sheet-change
 

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.

New-sheet
 

Next Article

Similar Reads