VBA-Lesson 11.2. Worksheet Events (Worksheet Events)

In the previous lesson, we considered the events related to the whole book. Now we will focus on events related to a single sheet.

Worksheet_SelectionChange (Opening the workbook)

To execute event-based instructions for an individual worksheet, select the worksheet in the editor, then Worksheet:

The SelectionChange event will be added by default. This event is fired whenever the content of the range changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

For example, the following code adds fill colors to one or more selected cells and automatically removes the fill color from the previously selected range when that range changes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Static previous_selection As String

      If previous_selection <> "" Then
          'Removing background color from previous selection :
          Range(previous_selection).Interior.ColorIndex = xlColorIndexNone
      End If

      'Adding background color to current selection :
      Target.Interior.Color = RGB(181, 244, 0)

      "Saving the address of the current selection:
      previous_selection = Target.Address
End Sub

Worksheet_Activate (Worksheet activation event)

This event occurs when a worksheet is activated:

Private Sub Worksheet_Activate()

End Sub

Worksheet_Deactivate (Worksheet Deactivation Event)

This event occurs when another worksheet is activated:

Private Sub Worksheet_Deactivate()

End Sub

Worksheet_BeforeDoubleClick (Double-click event on a cell)

This event occurs when a worksheet cell is double-clicked:

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

End Sub

Worksheet_BeforeRightClick (Event before right click)

This event occurs before the right mouse click on the worksheet:

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

End Sub

Worksheet_Calculate (Worksheet Calculation Event)

This event occurs whenever data in a worksheet is calculated or recalculated:

Private Sub Worksheet_Calculate()

End Sub

Worksheet_Change (Cell Content Change Event)

This event occurs whenever the contents of the cells in the given sheet change:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Worksheet_FollowHyperlink (Link Click Event)

This event occurs when a link (hypertext) is clicked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

End Sub

Temporarily deactivate all events

To execute code without any events, insert it between two lines of code like this:

Application.EnableEvents = False ' => disable events
'Instructions
Application.EnableEvents = True ' => re-enable events

Articles on the topic: