In this article, I will discuss (in details) about the Excel VBA Worksheet Events and their uses. This is a very thorough guide and I have tried to explain it in layman language. If you fail to understand any topics, put your confusions in the comment box.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide and Learn Excel VBA Events Completely with 5 Tutorials.
Worksheet related events are some of the most useful. Learning how to use these events can benefit you most as these events provide your applications do impossible things.
The following table lists the most commonly used worksheet events with a brief description of each. Remember that if you are creating an event procedure for worksheet “Sheet1”, then the procedure must be entered into the code module for “Sheet1”. Click the “Sheet1” object in Project window to activate the code module for “Sheet1”.
Table of Contents
Table: Excel VBA Worksheet Events
|Event Name||Action that Executes the Event|
|Activate||When the worksheet is activated, this event executes.|
|BeforeDoubleClick||When the worksheet is double-clicked, this event executes.|
|BeforeRightClick||When the worksheet is right-clicked, this event executes.|
|Change||When any cell on the worksheet is changed, this event executes.|
|Deactivate||Before the worksheet is deactivated, it executes.|
|SelectionChange||When the selection on the worksheet is changed, it executes.|
Change Event, SelectionChange Event, and BeforeRightClick worksheet events are provided in the working file.
Using Worksheet Event: Change
The Change event is executed when the user change any cell in the worksheet. For the following cases the Change event is not triggered:
- When a different value is generated from a formula used in a cell.
- When an object is added to the worksheet. Example of objects: a chart, a shape, etc.
Relevant procedure for the Change event is “Worksheet_Change”. Worksheet_Change procedure has a Target argument. This argument is of Range (Range=Cell) object type. When you change something in a cell of a worksheet, that cell name is stored in the Target argument.
For example, if you change something in the cell named “Input”, then “Target= Input”. The following example displays a message box that shows the address of the Target range:
Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Range(cell) " & Target.Address & " has been changed." End Sub
After entering and saving the above code in the code module for the worksheet “Sheet1”, I entered 50 in cell B6 and pressed enter. The following image is showed:
In some cases, the Change event will not work as per your expectation. For example:
- If you change the formatting of a cell, it will not execute the Change event. But choosing Home ➪ Editing ➪ Clear ➪ Clear Formats will trigger the Change event.
- Pressing Delete will trigger the Change event, even the cell is empty when you press Delete.
- Worksheet cells changed using Excel commands may or may not execute the Change event. For example, working with sorting and goal-seeking operations in a worksheet cell will not execute the Change event. But if you work with operations such as Find and Replace, use the AutoSum button, or if you add a Totals row to a table, then Change event will be triggered.
- If a VBA procedure changes a worksheet cell, then the Change event is executed.
How to Monitor a specific range for changes
The Change event executes when any cell on the worksheet changes. But maximum time you want that changes made to a specific cell or cell range will be counted. How do you do that?
Read More: Workbook-level Events and Their Uses
Let’s make it clear with an example. Say you have a cell range A1:E10 and you have named it InputRange. If you don’t know how you will name the cell range A1:E10 as InputRange, follow these simple steps:
- Select cell range A1: E10
- Right click on the selected cells
- A shortcut menu will appear
- Choose Define Name… option from the shortcut menu
- The New Name dialog box appears
- Enter InputRange in the Name field of the New Name dialog box
- Finally click OK to close the dialog box.
I am now going to write the Worksheet_Change event-handler procedure in such way that whenever a user changes something in the cell range InputRange(A1:E10), a message box will pop up with the information which cell has been changed.
The following code shows the first and last statement of the Worksheet_Change event-handler procedure:
Private Sub Worksheet_Change(ByVal Target As Range) End Sub
You see that the Worksheet_Change event-handler procedure has a Target argument. It is of Range object type. Target corresponds to a worksheet cell that has been changed by a user. It means that say you have made some changes in the cell F15, then the Target argument= F15.
Worksheet_Change event-handler Procedure
Now closely observe the following Worksheet_Change procedure:
Private Sub Worksheet_Change(ByVal Target As Range) 'Cell A1:E10 is named as InputRange Dim VRange As Range Set VRange = Range("InputRange") If Union(Target, VRange).Address = VRange.Address Then MsgBox "The changed cell is in the Input Range." End If End Sub
The code is explained line by line below:
- ‘Cell A1:E10 is named as InputRange: It is just a comment.
- Dim VRange As Range: A new variable VRange is created. It is a Range type object. Range type object means that this variable(VRange) can store only worksheet cell/range reference.
- Set VRange = Range(“InputRange”): VRange variable now contains InputRange(A1: E10) reference.
- If Union(Target, VRange).Address = VRange.Address Then
MsgBox “The changed cell is in the Input Range.”
The above three line code explains that if Union(Target, VRange).Address = VRange.Address, then MsgBox will pop-up this statement: “The changed cell is in the Input Range”, otherwise if Union(Target, VRange).Address ≠ VRange.Address then nothing will happens.
Read More: How to Use Non-object Events
In the following quotation, I have explained Union function in VBA.
Union function in VBA: Lets explain it with a real-time example. Say a user has done some changes in cell B5. Then the above Worksheet_Change procedure’s Target argument will hold the reference of cell B5. Simply you can think like this: Target=B5. And you know VRange is set to hold InputRange(A1: E10), so VRange= InputRange= A1:E10. So Union(Target,VRange).Address= Union( B5, A1:E10).Address= Union(A1: E10).Address= A1: E10= VRange.Address. So for change in cell B5 will execute the statement MsgBox “The changed cell is in the Input Range.”
Our preceding Worksheet_Change procedure has a bug: a user can make changes in more than one cell, so Target argument may contain a range. Therefore, the procedure requires to check all the cells in Target. So my procedure has to check each changed cell and if the cell is within the range, then a message box will be displayed with some information.
Private Sub Worksheet_Change(ByVal Target As Range) Set VRange = Range(“InputRange”) For Each cell In Target If Union(cell, VRange).Address = VRange.Address Then Msgbox “The changed cell is in the input range.” End if Next cell End Sub
The above code will generate three message boxes(one under another) shown in the following image when a user makes changes in the cell range C5: C8.
Using Worksheet Event: SelectionChange
The following example procedure explains how a a SelectionChange event works. It executes whenever the user makes a new selection on the worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 40 .EntireColumn.Interior.ColorIndex = 40 End With End Sub
Whenever a user selects a worksheet cell, the entire column and row will be shaded to make it easily identify. For example, if a user selects cell B5, then whole column(B column) and whole row(5) of this cell will be shaded. See the figure below:
Using Worksheet Event: BeforeRightClick
Just think that you don’t want to show a shortcut menu when you right click on a worksheet cell. In this case you can use this Worksheet event. The following procedure makes the Cancel argument to TRUE. This cancels the working of the RightClick event that would show a shortcut menu. Instead, a message box appears.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Cancel = True MsgBox "The shortcut menu is not available." End Sub
So, this was all from me on this topic. Let me know in case you face any issues while using this article.
Download Working File
Download the working file from the link below:
Happy Excelling ☕