Worksheet Events in VBA
Learn how to execute VBA code automatically when worksheet cells change, users select cells, data is calculated, or a worksheet becomes active. Understand event procedures, event arguments, validation, and practical Excel automation examples.
1. What Are Worksheet Events?
Worksheet events are special VBA procedures that execute automatically when a specific action occurs on a worksheet. Unlike a normal macro, a worksheet event does not usually need to be started manually by clicking the Run button.
For example, when a user changes a value in cell A1, VBA can automatically validate the value, display a message, highlight a cell, or update another part of the worksheet.
2. Common Worksheet Events
Excel provides several worksheet events. Each event is associated with a particular action.
| Event | When It Occurs |
|---|---|
Worksheet_Change |
When cells are changed by a user or an external link. |
Worksheet_SelectionChange |
When the selected cell or range changes. |
Worksheet_Activate |
When the worksheet becomes active. |
Worksheet_Deactivate |
When the worksheet is deactivated. |
Worksheet_BeforeDoubleClick |
Before a cell is double-clicked for its normal action. |
Worksheet_BeforeRightClick |
Before the normal right-click shortcut menu appears. |
Worksheet_Calculate |
When the worksheet recalculates. |
Worksheet_FollowHyperlink |
When a hyperlink on the worksheet is followed. |
Worksheet_Change
does not fire simply because a formula result changes.
Use Worksheet_Calculate when you need to respond
to recalculation.
3. Where to Write Worksheet Event Code
Worksheet event procedures must be placed in the code module of the worksheet for which the event should run. They should not be placed in a standard module such as Module1.
Steps to open the worksheet code module
- Open Excel and press
Alt + F11. - Locate the Project Explorer in the VBA editor.
- Expand the Microsoft Excel Objects folder.
- Double-click the required worksheet, such as Sheet1.
- In the code window, select Worksheet from the left dropdown.
- Select the required event from the right dropdown.
- Write the statements inside the generated event procedure.
4. Worksheet_Change Event
The Worksheet_Change event runs when a cell or
range on the worksheet is changed. Its Target
argument represents the cell or range that was changed.
Syntax
Private Sub Worksheet_Change(ByVal Target As Range)
' Write event statements here
End Sub
Example 1: Display the changed cell address
Place this code in the required worksheet's code module. When a cell changes, Excel displays its address.
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Changed cell: " & Target.Address
End Sub
If cell B3 is changed, the message displays
Changed cell: $B$3.
Example 2: Respond only to changes in A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
MsgBox "Cell A1 has changed."
End If
End Sub
The Intersect function checks whether the
changed range overlaps the specified cell.
Target may contain
more than one cell.
5. Using Intersect with Target
Intersect determines whether two ranges overlap.
It is commonly used to restrict an event procedure to a
particular cell or range.
Example 3: Monitor changes in column B
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Columns("B")) Is Nothing Then
Exit Sub
End If
MsgBox "A value in column B was changed."
End Sub
The message appears only when the changed range overlaps column B. Changes elsewhere in the worksheet are ignored.
Example 4: Monitor a specific range
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B2:B10")) Is Nothing Then
Exit Sub
End If
MsgBox "A cell in B2:B10 was changed."
End Sub
Intersect before
processing Target when the event should respond
only to a specific part of the worksheet.
6. Handle Multiple-Cell Changes
When a user pastes a block of data, the Target
argument can represent several cells. The event code should
be designed to handle this possibility.
Example 5: Check changes in A2:A20
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedRange As Range
Set changedRange = Intersect(Target, Me.Range("A2:A20"))
If changedRange Is Nothing Then
Exit Sub
End If
MsgBox changedRange.CountLarge & _
" cell(s) in A2:A20 were affected."
End Sub
CountLarge returns the number of cells in the
intersecting range and can handle large cell counts.
Example 6: Process each changed cell
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedRange As Range
Dim cell As Range
Set changedRange = Intersect(Target, Me.Range("A2:A20"))
If changedRange Is Nothing Then
Exit Sub
End If
For Each cell In changedRange.Cells
If Not IsError(cell.Value) Then
If Len(cell.Value) > 0 Then
Debug.Print cell.Address, cell.Value
End If
End If
Next cell
End Sub
This example processes each non-empty, non-error cell in the affected portion of A2:A20. The output appears in the VBA editor's Immediate window.
7. Practical Example: Validate User Input
Worksheet events can validate data immediately after a user enters it. For example, suppose cells B2:B20 should contain numeric marks between 0 and 100.
Example 7: Validate marks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedRange As Range
Dim cell As Range
Set changedRange = Intersect(Target, Me.Range("B2:B20"))
If changedRange Is Nothing Then
Exit Sub
End If
For Each cell In changedRange.Cells
If Not IsError(cell.Value) Then
If Len(cell.Value) > 0 Then
If Not IsNumeric(cell.Value) Then
MsgBox "Enter numeric marks only.", vbExclamation
Exit For
End If
If CDbl(cell.Value) < 0 Or CDbl(cell.Value) > 100 Then
MsgBox "Marks must be between 0 and 100.", vbExclamation
Exit For
End If
End If
End If
Next cell
End Sub
This code checks numeric values and the permitted range. It displays a warning when invalid input is detected.
8. Undo Invalid Input Safely
The Application.Undo method can undo the most
recent user action. It can be useful for rejecting invalid
input, but it must be used carefully because it reverses the
user's last action.
Example 8: Reject invalid single-cell input
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B2:B20")) Is Nothing Then
Exit Sub
End If
If Target.CountLarge > 1 Then
MsgBox "Please change one cell at a time.", vbExclamation
Exit Sub
End If
If IsError(Target.Value) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Error values are not allowed.", vbExclamation
Exit Sub
End If
If Len(Target.Value) = 0 Then Exit Sub
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Enter a numeric value.", vbExclamation
Exit Sub
End If
If CDbl(Target.Value) < 0 Or CDbl(Target.Value) > 100 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Enter a value between 0 and 100.", vbExclamation
End If
End Sub
The example restricts the operation to one changed cell and uses Undo when validation fails.
Application.Undo
reverses the last user action and can affect a paste or another
operation. In production code, preserve and restore application
state reliably, and test every exit path.
9. Worksheet_SelectionChange Event
The Worksheet_SelectionChange event runs whenever
the selected cell or range changes. Its Target
argument represents the new selection.
Syntax
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Write selection-related statements here
End Sub
Example 9: Display the selected cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge = 1 Then
Debug.Print "Selected cell: " & Target.Address
End If
End Sub
When a single cell is selected, its address is printed in the Immediate window.
Example 10: Highlight the selected cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge <> 1 Then Exit Sub
Target.Interior.Color = RGB(255, 255, 153)
End Sub
10. Worksheet_Activate and Worksheet_Deactivate
These events run when a worksheet becomes active or is deactivated. They are useful for displaying instructions, preparing a worksheet, or updating information when users move between sheets.
Example 11: Worksheet_Activate
Private Sub Worksheet_Activate()
Me.Range("A1").Value = "Welcome to the Sales Report"
Me.Range("A1").Font.Bold = True
End Sub
Whenever this worksheet becomes active, the code writes a heading into A1 and makes the text bold.
Example 12: Worksheet_Deactivate
Private Sub Worksheet_Deactivate()
Debug.Print "Sales Report worksheet deactivated."
End Sub
The second example writes a message to the Immediate window when the user leaves the worksheet.
11. Worksheet_BeforeDoubleClick Event
The Worksheet_BeforeDoubleClick event occurs
before Excel performs the normal double-click action.
The Cancel argument can prevent that action.
Example 13: Prevent editing by double-clicking
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A1:D20")) Is Nothing Then
Cancel = True
MsgBox "Double-click editing is disabled in this range."
End If
End Sub
When a cell inside A1:D20 is double-clicked, the message appears and Excel's normal double-click action is cancelled.
Cancel argument is
useful when you need to intercept a double-click before
Excel performs its default action.
12. Worksheet_BeforeRightClick Event
The Worksheet_BeforeRightClick event runs before
Excel displays its normal right-click shortcut menu.
Example 14: Display a custom message
Private Sub Worksheet_BeforeRightClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
Cancel = True
MsgBox "Right-click is restricted in this range."
End If
End Sub
The shortcut menu is cancelled for cells inside A1:C10. Right-clicking outside that range follows Excel's normal behavior.
13. Worksheet_Calculate Event
The Worksheet_Calculate event runs after the
worksheet recalculates. It does not receive a
Target argument.
It is useful when a formula result changes and you need to respond to that recalculation. However, the event does not identify which formula cell changed.
Example 15: Display a recalculation message
Private Sub Worksheet_Calculate()
Debug.Print "Worksheet recalculated at " & Now
End Sub
Whenever the worksheet recalculates, the current date and time are written to the Immediate window.
14. Worksheet_FollowHyperlink Event
The Worksheet_FollowHyperlink event runs when
a hyperlink on the worksheet is followed.
Example 16: Track hyperlink usage
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Debug.Print "Hyperlink followed: " & Target.Address
End Sub
The hyperlink's address is printed in the Immediate window.
The Target argument here is a Hyperlink object,
not a Range object.
15. Application.EnableEvents
The Application.EnableEvents property controls
whether Excel events are enabled. It is often used when
an event procedure changes worksheet cells and you need to
prevent the same event from running again.
Example 17: Update column C when column B changes
Assume column B contains quantities and column C should display double the quantity.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedRange As Range
Dim cell As Range
On Error GoTo ErrorHandler
Set changedRange = Intersect(Target, Me.Range("B2:B20"))
If changedRange Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In changedRange.Cells
If Not IsError(cell.Value) Then
If Len(cell.Value) = 0 Then
cell.Offset(0, 1).ClearContents
ElseIf IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = CDbl(cell.Value) * 2
Else
cell.Offset(0, 1).Value = "Check Input"
End If
Else
cell.Offset(0, 1).Value = "Check Input"
End If
Next cell
CleanExit:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbExclamation
Resume CleanExit
End Sub
The procedure checks changed cells in B2:B20 and updates the corresponding cells in column C. Events are disabled during the updates to prevent recursive event execution.
Application.EnableEvents.
16. Worksheet_Change vs Worksheet_SelectionChange
| Feature | Worksheet_Change | Worksheet_SelectionChange |
|---|---|---|
| Trigger | Cell contents change. | Selected range changes. |
| Argument | Target As Range |
Target As Range |
| Formula recalculation | Does not run solely because a formula result changes. | Does not run solely because a formula result changes. |
| Typical use | Validation, updating values, data entry automation. | Selection highlighting, contextual instructions. |
17. Common Mistakes to Avoid
- Writing worksheet event code in a standard module instead of the required worksheet module.
-
Assuming
Worksheet_Changeruns whenever a formula result changes. -
Assuming
Targetalways contains only one cell. - Comparing cell values without checking for errors or non-numeric input where appropriate.
-
Forgetting to restore
Application.EnableEventsafter disabling events. - Modifying worksheet cells inside an event without considering recursive event execution.
-
Using
Application.Undowithout considering its effect on the user's last action. - Performing time-consuming work on every selection change or recalculation.
- Assuming a worksheet event written in one sheet module automatically handles all other worksheets.
- Saving an event-based workbook in a format that does not preserve VBA macros.
18. Practice Exercises
Complete the following tasks in Excel VBA. Write each worksheet event in the appropriate worksheet code module and test it with different inputs.
- Display a message whenever cell A1 changes.
- Monitor changes only in the range B2:B20.
- Print the address of every changed cell in column C.
- Handle a paste operation that changes multiple cells.
- Validate that marks in B2:B20 are between 0 and 100.
- Display the selected cell address using SelectionChange.
- Highlight a selected cell and restore the previous cell's formatting.
- Write a heading whenever a worksheet becomes active.
- Display a message when a worksheet is deactivated.
- Cancel double-click editing in a specified range.
- Cancel the right-click shortcut menu in a specified range.
- Print a message whenever the worksheet recalculates.
- Track the address of a followed hyperlink.
- Update column C when values in column B change.
- Use error handling to ensure events are restored after a failure.
- Compare the behavior of Change and SelectionChange.
Important Points to Remember
- Worksheet events execute automatically when their associated actions occur.
- Place worksheet event procedures in the appropriate worksheet code module.
- Use
Worksheet_Changefor changes to cell contents. - Use
Worksheet_SelectionChangewhen the selected range changes. - Use
Worksheet_ActivateandWorksheet_Deactivatefor worksheet navigation actions. - Use
Intersectto restrict event handling to a particular range. - Always consider multi-cell changes when processing the Target range.
- Use
Worksheet_Calculatewhen responding to recalculation. - Use
Application.EnableEventscarefully and restore it after errors. - Save macro-enabled workbooks as
.xlsmwhen VBA code must be preserved.
Summary
Worksheet events allow VBA code to respond automatically to worksheet actions such as changing cell values, selecting ranges, activating or deactivating worksheets, double-clicking cells, right-clicking cells, following hyperlinks, and recalculating formulas.
By using the correct event procedure, checking the Target range,
validating data, handling errors, and restoring
Application.EnableEvents reliably, you can build
practical and dependable Excel automation for data validation,
worksheet updates, user interaction, and reporting.