Excel VBA Worksheet Events and Their Uses

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.

how to insert worksheet events


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.

view sheet 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.

select worksheet

  • 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.

select worksheet event

Read More: Workbook-level Events and Their Uses


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

Worksheet.Activate Event

  • Now you will see the following message whenever you go to Sheet2.

event output

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

Read More: How to Use Non-object Events


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

Worksheet.BeforeDelete Event

  • Now try to delete Sheet3. Then you will see the following message just before the sheet is deleted.

event output


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

Worksheet.BeforeDoubleClick Event

  • 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.

event output

  • 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.

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

Worksheet.BeforeRightClick Event

  • After that, you will see the following result whenever you right-click on a cell.

event output

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

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

Worksheet.Calculate Event

  • After that, you will see the following message whenever any formula gets recalculated in Sheet6.

event output

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

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

Worksheet.Change Event

  • 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.

event output

Remarks:

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


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

Worksheet.Deactivate Event

  • Now you will see the following message every time you click away from Sheet8.

event output


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

Worksheet.FollowHyperlink Event

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

event output


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

Worksheet.PivotTableUpdate

  • After that, you will see the following message when the pivot table gets updated.

event output


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

Worksheet.SelectionChange Event

  • After that, change the selection on that sheet to see the following result.

event output


Similar Readings


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.


Further Readings

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo