In my previous article we discuss about what are the different Workbook level events. Today we discuss about different Worksheet events with examples. Worksheet events are events executed at Worksheet level.

Worksheet_Activate(): This event executes whenever we open the particular work sheet for which we add this event.

Private Sub Worksheet_Activate()

      MsgBox ("This is Worksheet Activate Event")

End Sub

When we add the above code for any sheet, it executes and will show alert whenever you select that particular sheet.

Worksheet_Deactivate(): This event executes whenever you deselect the sheet for which you added this event as shown below.

Private Sub Worksheet_Deactivate()

 MsgBox ("This is Worksheet DeActivate Event")

End Sub

Above code displays the alert when we deselect the particular sheet.

Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean): This event fires whenever you double click on any cell in the specific sheet.

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

 Target.Interior.ColorIndex = 5

End Sub

The above code changes cell background Color to blue if we docuble clicks any cell.

Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean): This event triggers whenever we right click on the provided range for Target parameter.`

Worksheet_Calculate(): This event fires after any formula recalculated in the specific worksheet.

Forexample lets have some formula on cell C1 which displays the sum of cell’s A1 and B1. So the following code displays the value of cell C1 after every change for cell A1 and B1.

Private Sub Worksheet_Calculate()

 MsgBox (Range("C1"))

End Sub

Worksheet_Change(ByVal Target As Range): This executes after modifying the any cell in a sheet including edit, delete & enter.

Private Sub Worksheet_Change(ByVal Target As Range)

 MsgBox (Target.Value)

End Sub

The above code displays the cell value for which you do modifications.

Worksheet_SelectionChange(ByVal Target As Range): This event fires when you select the new range in the sheet.

Worksheet_FollowHyperlink(ByVal Target As Hyperlink): This executes whenever we click on any hyperlink in a given sheet.

Worksheet_LensGalleryRenderComplete: This event fires whenever we select the Quick Analysis Tool in the Microsoft Office Excel.

Worksheet_PivotTableUpdate(ByVal Target As PivotTable): This event occurs whenever any Pivot table is updated in the sheet.

Worksheet_PivotTableAfterValueChange(ByVal TargetPivot As PivotTable, TargetRange As Range): This event executes whenever any cell within the PivotTable modifies.

Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean): This event fires when Pivot tables updates from OLAP data source.

Worksheet_PivotTableBeforeCommitChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean): This event occurs whenever OLAP pivot table updates its data source.

Worksheet_PivotTableBeforeDiscardChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long): This executes when pivot table discard the changes from its data source.

Worksheet_PivotTableChangeSync(ByVal Target As PivotTable): Worksheet_PivotTableChangeSync event fires whenever particular Pivottable has beem changes and Target is the PivotTable that has been changed.