Excel VBA Worksheet Events and Their Uses

Worksheet events in Excel are actions that trigger the macros corresponding to the particular events. Assume you have just selected Sheet1. This action of yours activates Sheet1. So, this is a worksheet activation event. Such other worksheet events are deleting a sheet, right-clicking, changing cells, exiting from a sheet, updating a pivot table, etc. You can add macros in excel to trigger them automatically whenever you perform such actions.

Download Practice Workbook

Use of Worksheet Event(s).xlsm

How to Add Worksheet Events in Excel

Steps:

Right-click on the sheet tab and select View Code.

The VBA code module for that sheet will open. You can also press ALT + F11 and double-click on the desired sheet. Choose Worksheet instead of General using the object dropdown to access the events.

The SelectionChange event will automatically be inserted. You need to click on the event dropdown right next to the object dropdown to choose the desired event. You can enter the code inside the private subject procedure to get the desired result.

Excel VBA Worksheet Related Events and Their Uses

Example 1 – Activate

This event triggers whenever you go to that specific sheet. It will occur whenever you activate a worksheet by clicking on the sheet tab.

Suppose you want to show a message whenever users go to Sheet2, you can use the following code in Sheet2.

Private Sub Worksheet_Activate()
MsgBox "You have selected Sheet2"
End Sub

You will see the following message whenever you go to Sheet2.

Remarks: This event won’t occur if you create a new window. But it will trigger when you switch between windows in the same workbook.

Example 2 – BeforeDelete

This event occurs whenever you try to delete a worksheet. As the name suggests, this event will trigger just before the worksheet gets deleted.

Suppose you want to show a confirmation message before deleting Sheet3, you can use the following code in Sheet3.

Private Sub Worksheet_BeforeDelete()
MsgBox "You have permanently deleted Sheet3."
End Sub

When you try to delete Sheet3, you will see the following message just before the sheet is deleted.

Example 3 – BeforeDoubleClick

This event occurs when you double-click on a cell in the worksheet and just before it takes you to the cell editing mode.

When you double-click on a cell, excel goes to the cell editing mode. Suppose you want to remind the users not to edit in any cell of Sheet4, you can use the following code in that sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "You should not edit cells on this sheet."
End Sub

Whenever you double-click on a cell in that sheet, you will see the following message before excel allows you to edit the cell.

If you want this to happen when users double-click on cells only within a specific range, then apply the following code instead.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A1:J12")) Is Nothing Then Exit Sub
MsgBox "You should not edit cells in this range."
End Sub

Remarks: This event won’t occur if you double-click on the borders of cells.

Example 4 – BeforeRightClick

This event occurs when you right-click on a cell or range before showing the right-click options.

Suppose you need to show a message including the active cell reference whenever a user right clicks on a cell in Sheet5, you can use the following code in Sheet5.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "You've right-clicked on " & ActiveCell.Address
End Sub

You will see the following result whenever you right-click on a cell.

Remarks: This event won’t trigger by right-clicking on a shape, menu bar, toolbar, or command bar.

Example 5 – Calculate

This event occurs when formulas in the worksheet get recalculated due to changes in reference values.

Suppose you need to show a popup message whenever any formula in Sheet6 gets recalculated, you can use the code below in Sheet6.

Private Sub Worksheet_Calculate()
MsgBox "Sheet6 recalculated " & Now
End Sub

You will see the following message whenever any formula gets recalculated in Sheet6.

Remarks: This event won’t occur if you manually recalculate the sheet from the Formulas tab.

Similar Readings

Example 6 – Change

This event triggers when you change any cell within the target range in the sheet.

Suppose you want to keep cell A1 in Sheet7 unchanged and you want to show a popup message whenever any user changes that cell to undo the changes, you can use the following code in Sheet7.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
MsgBox "Please undo the changes."
End Sub

When you try to change the contents in cell A1 in Sheet7, you will see the following pop-up message. You need to remove the If Intersect statement to apply this to the entire worksheet.

Remarks:

Change of cells due to recalculation will not trigger this event.

Example 7 – Deactivate

This event occurs if you deactivate a worksheet by going to another sheet.

If you want to show a popup message whenever users leave Sheet8, you can use the following code in that sheet.

Private Sub Worksheet_Deactivate()
MsgBox "You're leaving Sheet8."
End Sub

You will see the following message every time you click on another worksheet besides Sheet8.

Example 8 – FollowHyperlink

This event occurs when you click on a hyperlink within the worksheet.

If you need to remind the users that they will be redirected to the browser when they click on the hyperlinks in the Sheet9, you can use the following code in this worksheet.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "You'll be redirected to your browser."
End Sub

You will see the following message if you click on the hyperlink in this sheet.

Example 9 – PivotTableUpdate

This event occurs when a PivotTable in the worksheet gets updated.

Suppose you have created a pivot table in Sheet10 and you want to show a notifying message whenever the table is updated, you can use the following code in Sheet10.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox "PivotTable updated " & Now
End Sub

You will see the following message when the pivot table gets updated.

Example 10 – SelectionChange

This event occurs when you change the selection on a worksheet.

Suppose you need to highlight the rows and columns when a user selects a cell or a group of cells in Sheet11, you can use the following code in that sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Selection
.EntireRow.Interior.ColorIndex = 40
.EntireColumn.Interior.ColorIndex = 40
End With
End Sub

When you change the selection on that sheet, you will see the following result.