Event handler is an important portion of the VBA code. If you are one of those who want to learn more about VBA macros, this article would obviously come in handy. In this article, we’re going to narrate to you the VBA event handler in Excel.
What Is VBA Event Handler in Excel?
Let’s start with an example. Say, I have placed a Command Button in my worksheet like the following figure. We will discuss the detailed process later in this article.
We want that Command Button will do something when it is clicked. So here, clicking on the CommandButton1 is an event. What this command button will do when it is clicked depends on the underlying subprocedure written by you. Say we want this when this command button will be clicked, cells in the range B4:D4 will be decorated with the yellow background color. So, we write a code like the following:
Private Sub CommandButton1_Click()
Range("B4:D4").Interior.Color = vbYellow
End Sub
This sub-procedure is called event handler as it executes when someone clicks on the Command Button.
When I click the CommandButton1, cells in the range B4:D4 in my worksheet get yellow as their background color. An event happens and the event handler executes.
How to Get the Relevant Code Module to Put Event Handler?
All event handler procedures, I mean VBA code, must be placed in the relevant code module. If you are working with workbook-related events, then you should place the VBA code you write in the ThisWorkbook code module. Follow the steps below for clarification.
- At the very beginning, go to the Developer tab.
- Following this, click on Visual Basic in the Code group.
- Alternatively, press ALT + F11 to do the same task.
- Instantly, the Microsoft Visual Basic for Applications window appears.
- In Project Explorer, double-click on ThisWorkbook.
- Based on our previous action, the Code Module pops up.
- Then, from the left drop-down, select Workbook as the object.
- After that, from the top-right drop-down, select the Open event.
- Thus, the sub-procedure name looks like the one below.
Now, between these two lines, you can enter your Workbook event handlers, which means the VBA code. Also,
if you want to add Worksheet related event handlers, just look at the following steps.
- First of all, bring the Microsoft Visual Basic for Applications window like before.
- Then, double-click on the Sheet Name in which you want to insert the VBA code. In this case, we clicked on Sheet1.
- From the left drop-down, select Worksheet.
- After selecting Worksheet, the SelectionChange event gets selected automatically by default. Additionally, you can choose any other event from the right drop-down according to your preference.
Now, it is ready to take your input between these two lines.
Event Handler Procedures Have Predefined Names
Every event handler (the procedure or VBA code that controls the event) has a predefined name.
For example, the figure below shows the code module for the Sheet3 worksheet. To get this code module.
- First, double-click on Sheet3 (Highlighting Active Row) in Project Explorer.
- Then, select Worksheet from the left drop-down list,
- Finally, choose and click on the Activate event.
Therefore, we got a sub-procedure with two statements: the first statement is just the name of the sub-procedure, and the second statement is the End Sub statement, like the following figure.
There is no other code inside these two statements. The sub-procedure name is predefined by Excel. We have to write the event handler VBA code between these two lines.
VBA Event Handler: 5 Examples
Currently, we’ll discuss some practical VBA event handlers and see how you might use them in your daily life assignment. So, let’s explore them one by one.
Example 01: Opening Workbook
Let’s assume you want to prompt the end user to complete their time tracker each time they open a particular workbook. So, without further delay, let’s see how we do it.
- At the very beginning, move to the Developer tab.
- Then, select Visual Basic in the Code group.
- Alternatively, press ALT + F11 to replicate the task in the shortcut.
- Immediately, the Microsoft Visual Basic for Applications window appears.
- In Project Explorer, double-click on ThisWorkbook.
Thus, the Workbook Code Module opens.
- Presently, select Workbook on the left drop-down.
- Also, choose Open events from the right drop-down.
- Correspondingly, the following code appears in the module box automatically.
- Now, write some lines of code between these two lines as event handlers.
- Simply paste the following code into your module.
Private Sub Workbook_Open()
wkd = Weekday(Date)
If wkd = 5 Then MsgBox "Make Sure to Fill the Time Tracker"
End Sub
Now, once you open the worksheet (on Thursday) that contains this script, a message box containing the desired message Make Sure to Fill the Time Tracker will appear.
- Then, click OK to remove it from the screen.
Read More: Excel VBA Open Workbook Event
Example 02: VBA Event Handlers with Arguments
Event-handler procedures may also contain arguments. In this example, we’ll explain how to work with an event that will display a message when you activate any of the worksheets. Let’s see the process.
📌 Steps:
- Firstly, bring up the ThisWorkbook code module like Example 01.
- Then, select Workbook from the left drop-down.
- After that, choose SheetActivate on the left drop-down.
Thus, we’ll see the sub-procedure will automatically get the Sh argument.
- At this moment, paste the following code into the module.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name & “ was activated. ”
End Sub
This procedure has one argument: Sh. The Sh represents the activated sheet; it means that if you activate Sheet2, the value of Sh will be Sheet2; if you activate Sheet5, the value of Sh will be Sheet5, and so on. The Sh is declared as an Object.
The above code makes use of information passed through the argument Sh. The code finds out the name of the worksheet by accessing the argument’s Name property.
- First of all, open the working file after downloading it.
- Then travel from one worksheet to another.
- When you will travel from one worksheet to another, it will display the name of the activated worksheet like the following image.
Example 03: VBA Event Handler with Boolean Arguments
Some event-handler procedures have a Boolean argument called Cancel. For example, you may plan for a pop-up message that will display some information when you want to print something related to your workbook. There is an event called the BeforePrint event. Follow the entire process.
📌 Steps:
- Initially, open the ThisWorkbook code module.
- Later, write the following code.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Msg = "Have you checked your printer connection?"
Ans = MsgBox(Msg, vbYesNo, "About to print...")
If Ans = vbNo Then Cancel = True
End Sub
The default value of Cancel is FALSE. If you can make the value of Cancel to TRUE then the printing will be canceled.
The Workbook_BeforePrint procedure executes just before the workbook prints. When you try to print something, this event handler procedure will display a message to let you know whether you have checked the printer connection. If you press Yes (Cancel is FALSE), printing will start; if you press No (Cancel is TRUE), nothing will be printed.
- At first, press the CTRL key followed by P on your keyboard.
- Secondly, click on Print to print the document.
Therefore, you will find a message box like the following image.
Example 04: Highlighting Active Row
If your Excel dataset has a lot of columns, it becomes quite difficult to find data from one end to another end of a row. But if you generate a system in which whenever you select a cell in your dataset, the whole row will be highlighted, then you can easily find data from that row. Here, we’ll discuss this feature here. So, without further delay, let’s dive in!
📌 Steps:
- As always, open the code module, like before.
- Here, put the following code into the module.
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
Active_Row = Selection.Row
xRow = Active_Row
With Rows(Active_Row).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
- Presently, return to the worksheet.
- Following this, select cell E7.
Thus, the whole Row 7 gets highlighted in one click.
Example 05: Availing Advantage of Double-Click
In our following example, we’ll learn a remarkably interesting trick. Also, this event is very helpful to lessen your effort. This event sparks when someone double-clicks on a particular cell. Allow me to show the process below.
📌 Steps:
- As usual, bring up the code module like before.
- After that, paste the following code into the module.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Target
.Interior.Color = vbYellow
.Font.Color = vbBlack
.Font.Bold = True
End With
End Sub
The above code does the following things:
When you double-click on a cell, it will apply a yellow background color to the cell. Also, it makes the font color black and makes the font Bold.
- So, double-click on cells D6, E8, and H7 one after one.
Magically, we can see these cells get changed just by clicking.
Negative Impact of Event Handler on Undo Stack
I’ll start by explaining what an Undo Stack is.
Excel continuously tracks your actions while you work. You can always use CTRL + Z to return to the earlier step if you make a mistake. You can go back two steps by pressing CTRL + Z twice. The Undo stack contains a record of the actions you’ve taken.
If any event handler changes the worksheet, it can’t be undone by pressing CTRL + Z. So, the undo stack gets disabled at this time. Follow the steps below for a better understanding.
📌 Steps:
- Press CTRL + Z to undo the highlighting of the cell and get Bold of the fonts.
But, nothing happened this time. The cells remain unchanged.
- Then, select cell D9.
- After that, make the font Bold.
- Later, click on the Fill Color dropdown.
- Next, select Yellow as the background color.
Actually, we make these changes manually instead of using the VBA event.
Subsequently, the cell looks like the one below.
- Now, press CTRL + Z on your keyboard.
At this time, the actions get undone.
Here is what we are trying to make you understand when we apply some changes through the event handler, the action can’t be undone using the Undo stack.
VBA Change Event
Change is an event of Worksheet. When some changes happen in a cell, the Change event gets triggered. Let’s see this with an example.
📌 Steps:
- Forthwith, open the code module like before.
- Following this, paste the code into that module.
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
MsgBox Target.Value
End Sub
- Secondly, select cell E6. Note that the cell value is 218.
- Then, change the cell value to 510.
- Additionally, press ENTER.
- As soon as the change gets placed, Excel shows a MsgBox with the cell address E6.
- Hence, click OK.
- Just after clicking OK, another MsgBox appears with a changed cell value of 510.
- Also, click OK to get rid of the box.
VBA Click Event
Actually, the VBA Click event works with the Command Button. When you click on the Command Button, the event triggers itself. Just follow us.
📌 Steps:
- Primarily, proceed to the Developer tab.
- Secondarily, click the Insert drop-down on the Controls group.
- After that, select Command Button (ActiveX Control).
- Later, drag the cursor like in the image below to place the button into the sheet.
Here is our CommandButton1 on the worksheet.
- Now, right-click on the button to open the context menu.
- Hereafter, select View Code from the options.
The code module window appears before us.
- Then, place the following code into the module.
Private Sub CommandButton1_Click()
Range("B10:H10").ClearContents
End Sub
- Lastly, as we click the CommandButton1, the contents in the B10:H10 range get cleared.
So, this was all from me on this topic.
You may download the following Excel workbooks for better understanding and practice yourself.
Conclusion
Thank you for reading this article. I hope all the information mentioned above about the VBA event handler will now prompt you to apply them in your Excel spreadsheets more effectively. Don’t forget to download the Practice file. If you have any questions or feedback, please let me know in the comment section.
