Excel VBA Worksheet Related Events and Their Uses

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

Excel VBA Worksheet Events

Activate and SelectionChange events procedures are entered into Sheet1 code module.

Table: Excel VBA Worksheet Events

Event NameAction that Executes the Event
ActivateWhen the worksheet is activated, this event executes.
BeforeDoubleClickWhen the worksheet is double-clicked, this event executes.
BeforeRightClickWhen the worksheet is right-clicked, this event executes.
ChangeWhen any cell on the worksheet is changed, this event executes.
DeactivateBefore the worksheet is deactivated, it executes.
SelectionChangeWhen 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:

Worksheet-related Events and Their Uses

Change event for worksheet “Sheet1” is executed.

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:

  1. Select cell range A1: E10
  2. Right click on the selected cells
  3. A shortcut menu will appear
  4. Choose Define Name… option from the shortcut menu
  5. The New Name dialog box appears
  6. Enter InputRange in the Name field of the New Name dialog box
  7. Finally click OK to close the dialog box.
Worksheet-related Events and Their Uses

Cell Range A1:E10 is named InputRange.

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.”
    End If
    :
    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.

Worksheet-related Events and Their Uses

Change Event is executed when some changes have been made in cell range C5: C7.

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:

Worksheet-related Events and Their Uses

Move the cell cursor. Active cell’s row and column will be shaded.

Caution: This procedure’s first statement Cells.Interior.ColorIndex = xlNone clears all the background shading applied to the worksheet. But if the shading is the result of a style applied to a table, then the macro doesn’t erase the table’s background shading.

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
Worksheet-related Events and Their Uses

BeforeRightClick Event cancels the RightClick event if Cancel argument is made TRUE.

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:

Worksheet-Events.xlsm

Happy Excelling ☕


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply