Excel 2019

Worksheet Change and SelectionChange Events

In this tutorial, we’ll discuss the Change and ChangeSelection worksheet events. The Worksheet_Change event-handler procedure executes whenever any cell in the worksheet is changed and Worksheet_SelectionChange event-handler procedure executes when the selection on the worksheet is changed.

The worksheet event-handler procedures must be in the code module for that worksheet. Put them somewhere else, and they won’t work. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

Worksheet view code window

Worksheet_Change event procedure

The Change event triggers whenever any cell in the worksheet is changed. Excel uses the Worksheet_Change event-handler procedure to trap the Change event. The Worksheet_Change procedure accepts Target (the Range object) as the parameter which represents the cell that was changed. The following example displays a message box that shows the address of the Target range:

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox Target.Address
End Sub

Try making some changing in cells, every time you make changes, a message box displays the address of the cell that changed.

Monitor changes made to specific cell or range

The Worksheet_Chnage procedure receives the Target as Range object which represents the changed cell(s). In this example, we compare the Target with the given cell range A1:A10 using Intersect method:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim MyRange As Range
 Set MyRange = Range("A1:A10")
 If Not Intersect(Target, MyRange) Is Nothing Then
  MsgBox ("You've changed the " & Target.Address)
 End If
End Sub

A popup message box appears when a change made in the given cell range:

Worksheet_Change Vs. Worksheet_Calculate

The Worksheet_Change event procedure is not executed by a calculation change, for example, when a formula returning a different value. You must use the Worksheet_Calculate event procedure to capture the changes to values in cells that contain formulas.

Worksheet_SelectionChange event procedure

The Worksheet_SelectionChange event procedure executes when a cell is selected. The following code highlights the active cell with a red color every time a different cell is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Cells.Interior.ColorIndex = 0
 Target.Interior.ColorIndex = 3
End Sub

The first statement removes the background color for all cells in the worksheet. Next, the the active cell is shaded with red color.

Take some action when specific cells or ranges selected

In many cases, you need to execute a piece of code when certain cells or ranges selected. To accomplish this, we use the Intersect method on the Target (selected cell or range) and the range containing the specific cell to verify the Target is one of the specific cells or ranges. If the Target is in the range containing the specific cells, you can execute the code.

The following code highlights the active cell with a red color every time a different cell is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Cells.Interior.ColorIndex = 0
 Dim MyRange As Range
 Set MyRange = Range("A1:A10")
 If Not Intersect(Target, MyRange) Is Nothing Then
  Target.Interior.ColorIndex = 3
 End If
End Sub
Advertisement:
Advertisement: