VBA Course: Worksheet Events

On the previous page, the events were related to the entire workbook. On this one, we will focus on events related to a sheet.

Worksheet_SelectionChange (on selection change)

To execute instructions based on an event for a specific sheet, select the sheet in the editor, then choose Worksheet:

The SelectionChange event is added by default, and it triggers when the selection changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

For example, here is a code that colors the selected cell or cells and automatically removes the color from the previous selection when the selection changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static previousSelection As String

    'Remove background color from the previous selection
    If previousSelection <> "" Then
        Range(previousSelection).Interior.ColorIndex = xlColorIndexNone
    End If

    'Color the current selection
    Target.Interior.Color = RGB(181, 244, 0)

    'Save the address of the current selection
    previousSelection = Target.Address

End Sub

Worksheet_Activate (on sheet activation)

This event triggers when the sheet is activated:

Private Sub Worksheet_Activate()

End Sub

For example, select cell D5 when the sheet is activated:

Private Sub Worksheet_Activate()
    
    Range("D5").Select
    
End Sub

Worksheet_Deactivate (on sheet deactivation)

This event triggers when another sheet in the workbook is activated:

Private Sub Worksheet_Deactivate()

End Sub

For example, clear the contents of cells B2 to B10 after leaving the sheet:

Private Sub Worksheet_Deactivate()
    
    Range("B2:B10").ClearContents
    
End Sub

Worksheet_BeforeDoubleClick (on double-click)

This event triggers when a cell in the sheet is double-clicked:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

For example, color the double-clicked cell green (or white if it is already colored):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Interior.Color = 16777215 Then 'If white
        Target.Interior.Color = RGB(200, 255, 100) 'Green color
    Else
        Target.Interior.Color = 16777215 'White color
    End If
    
End Sub

The double-click event can be canceled by setting the Cancel variable to True.

Worksheet_BeforeRightClick (on right-click)

This event triggers when the sheet is right-clicked:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub

For example, add the current date on right-click if the clicked cell is in column C:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    'If cell is in column 3 (C)
    If Target.Column = 3 Then
        Target = Date 'Add current date
        Cancel = True 'Cancel right-click
    End If
    
End Sub

The right-click event can be canceled by setting the Cancel variable to True (in this case, the context menu will not be displayed).

Worksheet_Change (on cell change)

This event triggers when the content of cells in the sheet is changed:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Temporarily Disable All Events

To execute code without triggering events, place it between these two lines:

Application.EnableEvents = False 'Disable events

'Instructions...

Application.EnableEvents = True 'Enable events