Worksheet Events in VBA Excel

Worksheet Events are important to learn. There are times when we want to run a macro/sub procedure when a cell changes its value when the user double clicks on any cell or when a sheet is activated. We need Worksheet Event Handlers in VBA for that to work.

So let us start learning about Worksheet Events.

What is Worksheet Event Handler?

A Worksheet Event Handler is like a Sub Procedure which is local to the Sheet Module. Worksheet events in VBA are coded only for Sheets collection objects. We need to define them in the Sheet Module. Whenever a particular event happens in the Sheet containing the corresponding event handler, it would be executed automatically.

You can download the practice workbook from here.

Example of Worksheet Event Handlers

There is a list of some available event handlers for the Worksheet.

• Worksheet_Change
• Worksheet_SelectionChange
• Worksheet_Activate
• Worksheet_Deactivate
• Worksheet_BeforeDelete
• Worksheet_BeforeDoubleClick

You can also see the list of event handlers using these steps.

• Right Click on the Sheet tab and choose the option View Code.

• This opens the Sheet Module for Sheet1. From there you can see the list of all available events.

Click on any of these event handlers like Change. and a corresponding event handler skeleton would be entered into the sheet module.

Worksheet_Change(ByVal Target As Range)

This event triggers when we make any change in the container worksheet (that contains the event handler). To do something when a change is made in the worksheet, we use this event handler.

Use the following Code in Sheet1 Module.

Private Sub Worksheet_Change(ByVal Target As Range)
'runs when there is change in worksheet
MsgBox ("You do something")
End Sub

When you do something in Sheet1, this code runs and displays a message box.

The Target in the Procedure argument is the Active Cell.

An example is to add today’s date in cell B2 when the value in cell A1 changes (Target must be cell A1). This event handler would do nothing for changes in any other cell of the worksheet if it is not cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
'runs when there is change in cell A1
If Target.Address = "$A$1" Then
Range("B2").Value2 = Format(Date, "mm/dd/yyyy")
End If
End Sub

Make a change in cell A1 and see that today’s date would be added to cell B2.

Worksheet_SelectionChange(ByVal Target As Range) Event

Worksheet_SelectionChange(ByVal Target As Range) Event executes whenever we change our selection or the active cell.

This code would check the row number of the active cell (before jumping to the next cell) and update the fill color of the cell if it is an even row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'runs when the active cell is in even row
If Target.Row Mod 2 = 0 Then
Target.Interior.ColorIndex = 28
End If
End Sub

Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

This event handler executes when the user double clicks on any cell of the container sheet. Here Target refers to the cell which is double clicked. You must know that when we double click a cell, we enter the edit mode. Cancel argument defines True or False. If Cancel is True, then upon double clicking, the cell would not go into the edit mode and the corresponding event handler runs. If Cancel is False then we enter the edit mode and the event handler executes.

This makes the cell Target Cell act as a button as the cell does not go into edit mode when Cancel=True.

This code runs when Target Cell is Cell A1. In simple words, when cell A1 of the container sheet is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'runs if cell A1 is double clicked
If Target.Address = "$A$1" Then
'do not enter into edit mode when target cell is A1
Cancel = True
'change interior color of cell A1
Target.Interior.ColorIndex = 22
End If
End Sub

Worksheet_Activate() and Worksheet_Deactivate() Event

Worksheet_Activate() and Worksheet_Deactivate() Event Handler run when the container sheet is either activated or deactivated. These two event handlers run when the worksheet containing the event handler is activated or deactivated respectively.

For instance, we can print a message when someone activates the Index Worksheet. Add a new worksheet and name it “Index”. Right click on the Index Sheet Tab and choose the option View Code. You need to place the code in the Index Sheet Module code.

Now when you go from Sheet1 to Index Sheet, A message box pops up.

Similarly, you can show a message box when Index Sheet is Deactivated.

You can also try many other Worksheet events.

This brings us to the end of Worksheet Events using VBA.

Thank you for reading.❤