We will highlight the worksheet-related events, how to add them in Excel VBA, and their uses in this article. 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. Actually, those macros are called worksheet events. The following GIF will give you an idea of how to add those events in excel.
Download Practice Workbook
You can download the practice workbook from the download button below.
How to Add Worksheet Events in Excel
Follow the steps below to add worksheet events in Excel VBA.
📌 Steps:
- First, right-click on the sheet tab and select View Code.
- Then, the VBA code module for that sheet will open as follows. You can also press ALT + F11 and double-click on the desired sheet there. Next, choose Worksheet instead of General using the object dropdown to access the events.
- After that, the SelectionChange event will be automatically inserted. You need to click on the event dropdown right next to the object dropdown to choose the desired event. Now you can write the code inside the private subject procedure to get the desired result.
Excel VBA Worksheet Related Events and Their Uses
Here we will discuss the most used events and their uses. So let’s begin.
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.
- Assume you want to show a message whenever users go to Sheet2. Then you can apply the following code in Sheet2.
Private Sub Worksheet_Activate()
MsgBox "You have selected Sheet2"
End Sub
- Now you will see the following message whenever you go to Sheet2.
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.
- Assume you want to show a confirmation message before deleting Sheet3. Then you can apply the following code in Sheet3.
Private Sub Worksheet_BeforeDelete()
MsgBox "You have permanently deleted Sheet3."
End Sub
- Now try to delete Sheet3. Then you will see the following message just before the sheet is deleted.
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. Now assume you want to remind the users about not editing any cell in Sheet4. Then you can apply 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
- Now 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
Read More: [Fixed!] Border Not Showing in Excel (6 Solutions)
4. BeforeRightClick
This event occurs when you right-click on a cell or range before showing the right-click options.
- Assume you need to show a message including the active cell reference whenever a user right clicks on a cell in Sheet5. Then you can apply 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
- After that, you will see the following result whenever you right-click on a cell.
5. Calculate
This event occurs when formulas in the worksheet get recalculated due to changes in reference values.
- Assume you need to show a popup message whenever any formula in Sheet6 gets recalculated. Then you can apply the code below in Sheet6.
Private Sub Worksheet_Calculate()
MsgBox "Sheet6 recalculated " & Now
End Sub
- After that, you will see the following message whenever any formula gets recalculated in Sheet6.
Similar Readings
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- How Different Is VBA from Other Programming Languages
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to Use VBA OnKey Event in Excel (with Suitable Examples)
- 22 Macro Examples in Excel VBA
6. Change
This event triggers when you change any cell within the target range in the sheet.
- Assume you want to keep cell A1 in Sheet7 unchanged. So you need to show a popup message whenever any user changes that cell to undo the changes. Then you can apply 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
- Now change the contents in cell A1 in Sheet7 to see the following message to pop up. You need to remove the If Intersect statement to apply this to the entire worksheet.
Change of cells due to recalculation will not trigger this event.
Read More: How to Use Select Case Statement in Excel VBA (2 Examples)
7. Deactivate
This event occurs if you deactivate a worksheet by going to another sheet.
- Assume you want to show a popup message whenever users leave Sheet8. Then you can apply the following code in that sheet.
Private Sub Worksheet_Deactivate()
MsgBox "You're leaving Sheet8."
End Sub
- Now you will see the following message every time you click away from Sheet8.
8. FollowHyperlink
This event occurs when you click on a hyperlink within the worksheet.
- Assume you need to remind the users that they will be redirected to the browser when they click on the hyperlinks in the Sheet9. Then you can apply the following code in this worksheet.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "You'll be redirected to your browser."
End Sub
- Now you will see the following message if you click on the hyperlink in this sheet.
9. PivotTableUpdate
This event occurs when a PivotTable in the worksheet gets updated.
- Assume you have created a pivot table in Sheet10. Now you want to show a notifying message whenever the table is updated. Then you can apply the following code in Sheet10.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox "PivotTable updated " & Now
End Sub
- After that, you will see the following message when the pivot table gets updated.
Read More: Excel VBA: Workbook Level Events and Their Uses
10. SelectionChange
This event occurs when you change the selection on a worksheet.
- Assume you need to highlight the rows and columns when a user selects a cell or a group of cells in Sheet11. Then you can apply 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
- After that, change the selection on that sheet to see the following result.
Read More: How to Alternate Row Colors in Excel Without Table (5 Methods)
Conclusion
Now you know about worksheet events, how to add them in VBA, and their uses. If you have further queries or suggestions, please let us know in the comment section below.
We have tried to introduce you to the most used worksheet events in this article. Therefore we didn’t attempt to show you the extensive uses of the events with various examples. However, you can leave a comment below if you are facing difficulty applying any of the worksheet events as you desire. We will try our best to help you. Moreover, this article does not include all of the worksheet events. You can see the full list of worksheet events here.
Please visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.