Using Excel Worksheet Change Events
To examine any change in a single or multiple cells in a worksheet, the worksheet change event is used. This must be placed in the Sheets section of the VBE, and not the general modules section.
There are two examples listed below:
- The first example only processes the change event if a single cells is modified (which is the normal mode when entering data)
- The second example (somewhat hypothetical) demonstrates processing changing cells only when multiple cells change at the same time (which occurs when you paste a range of cells to a new location).
The range of cells is passed to the change event code as the range variable "Target". As mentioned earlier, this will normally just be a single cell, but it can be multiple cells.
Program Code For A Single Cell Change Event
Option Explicit
' *********************************************************
' This Must Be Placed in the Sheet1 Microsoft Excel Objects
' Section, not the Modules Section
' ********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorEvent
' *********************************************************
' Only Allow A Single Cell Range
' *********************************************************
If Target.Cells.Count > 1 Then
Exit Sub
End If
' ********************************************************
' Turn Off EnableEvents To Prevent Looping
' ********************************************************
Application.EnableEvents = False
' ********************************************************
' Check For A Change in A1, And Say Hello In B1
' ********************************************************
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Cells(1, 2).Value = "Hello"
End If
ExitNormally:
' ********************************************************
' Return EnableEvents to Normal Mode
' ********************************************************
Application.EnableEvents = True
Exit Sub
ErrorEvent:
MsgBox Err.Description
Resume ExitNormally
End Sub
Program Code For A Multiple Cell Change Event
Option Explicit
' *********************************************************
' This Must Be Placed in the Sheet1 Microsoft Excel Objects
' Section, not the Modules Section
' *********************************************************
' *********************************************************
' Capitalize Any Cell Strings That Land Anywhere in the
' Range of A1 Through B4
' *********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngRangeToChange As Range
Dim C As Range
On Error GoTo ErrorEvent
' *********************************************************
' Only Accept If Multiple Cells Changed
' This Can Happen on a Paste of Copied Cells
' *********************************************************
If Target.Cells.Count < 2 Then
Exit Sub
End If
' *********************************************************
' Note That The Target Range Consists of Multiple
' Cells. Suppose there were 5 cells in the Target
' Range. They would be referenced as Target(1), Target(2),
' through Target(5). In this example, we don't use
' that notation.
' *********************************************************
' *********************************************************
' Turn Off EnableEvents To Prevent Loops
' *********************************************************
Application.EnableEvents = False
' *********************************************************
' Make Sure The Target of the Paste Intersects With At
' Least One Cell In The Range A1:B4
' *********************************************************
If Application.Intersect(Target, Range("A1:B4")) Is Nothing Then
Exit Sub
End If
Set rngRangeToChange = Application.Intersect(Target, Range("A1:B4"))
' *********************************************************
' Capitalize any cells that intersected the Range A1:B4
' *********************************************************
For Each C In rngRangeToChange
C.Value = UCase(C.Value)
Next C
' *********************************************************
' Turn On EnableEvents
' *********************************************************
ExitNormally:
Application.EnableEvents = True
Exit Sub
ErrorEvent:
MsgBox Err.Description
Resume ExitNormally
End Sub