So far in various articles, I have discussed Events and Event handlers in Excel VBA. To give you a fresh start, let’s have a look at the Excel VBA Events in detail through this article.
What Is an Event and Event Handler in Excel VBA?
To understand an Event and Event Handler, let us work with an example. Simply follow the steps below:
- First, prepare a dataset according to your preference. Here, we prepared one with the information of the Order List of 8 customers with Product ID and Price.
- Now, go to the Developer tab and select Insert.
- Here, choose Command Button from the ActiveX Controls section.
- Following, right-click on the Command Button and select Properties.
- Here, customize the Caption and ForeColor according to your preference.
- Next, insert this code and run it.
Private Sub CommandButton1_Click()
Range("B5").Interior.Color = vbBlue
End Sub
- Finally, you will see the output.
So what we learned from this example is Clicking on the Command Button is an Event. The Subprocedure will depend upon the code you write. This Subprocedure is Event Handler as it executes when someone clicks on the Command Button.
Where to Insert Event-Related Code in Excel?
Based on the type of event, we need to know where to put the code related to that object. Following are the five ways to operate it:
1. In Worksheet Code Window
For worksheet code, here is the process to insert code based on the specific object.
- First, select the worksheet object on your VBA Project window.
- Then, double-click on it and select Worksheet from the top-left corner of the window.
- Following, select any event from the Selection Change list according to your object.
- Lastly, insert your code as per the image below:
2. In ThisWorkbook Code Window
ThisWorkbook also works like the Worksheet code in the VBA project.
- First, select ThisWorkbook from the worksheet object list.
- Then, choose Workbook from the top-left corner panel.
- Lastly, select the type from the options and place your code.
3. In UserForm Code Window
In case you want to create a UserForm in Excel, you can apply the UserForm code.
- First, right-click on any worksheet object and select UserForm from the Insert section.
- That’s it, you will see the new window to insert the UserForm object.
4. In Chart Code Window
You can also type code if you have a Chart Sheet as your object. Simply follow the process below:
- First, select the Chart Sheet name from the worksheet object list.
- Then, select Chart from the drop-down menu.
- Lastly, select any event according to your code and insert your code.
5. In Class Module
Lastly, we can insert VBA code in the Class Module from the Insert section in any worksheet object.
Different Types of VBA Events in Excel
Excel works with a wide range of events. In this chapter, we’re going to deal with the following types of events:
1. Workbook Open Event
When a user tries to open a certain workbook holding the following code that event is the Workbook Open event. It will generally work as a welcome note.
Private Sub Workbook_Open()
MsgBox "Your Workbook"
End Sub
Read More: Excel VBA Open Workbook Event
2. Workbook NewSheet Event
This event triggers the opening of a new worksheet in Excel. It also allows a certain number of users in a shared workbook to avoid mess.
Private Sub Workbook_NewSheet(ByVal Obj As Object)
Application.DisplayAlerts = False
If Application.UserName <> "Maria" Then
Obj.Delete
End If
Application.DisplayAlerts = True
End Sub
Read More: Excel VBA: Workbook Level Events and Their Uses
3. Workbook BeforeSave Event
The BeforeSave event works when you want to save a workbook. Keep in mind that this event triggers in the first place, following saves the workbook.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox "Save the File in C Drive"
End Sub
4. Workbook BeforeClose Event
This event happens just before the workbook is closed by its user.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WkSh As Worksheet
For Each WkSh In ThisWorkbook.Worksheets
WkSh.Protect
Next WkSh
End Sub
5. Workbook BeforePrint Event
In excel, when you give the Print command or at least the Print Preview command, the BeforePrint event operates in excel.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each WkSh In Worksheets
WkSh.Calculate
Next WkSh
End Sub
6. Worksheet Activate Event
This event is triggered when you want to activate a sheet. This code unprotects a sheet just the moment it is activated.
Private Sub Worksheet_Activate()
ActiveSheet.Range("B5").Select
End Sub
7. Worksheet Change Event
This event occurs when you operate a change on your worksheet. However there are some distinctive situations where this code is successful. They are:
- Copy and pasting
- Clearing formatting
- Running a spelling check
Private Sub Worksheet_Change(ByVal Trg As Range)
MsgBox "Your recent change in Worksheet " & Trg.Address
End Sub
8. Workbook SelectionChange Event
The SelectChange event is triggered whenever there is any occurrence of a change within selected cells.
Private Sub Worksheet_SelectionChange(ByVal Trg As Range)
Application.Calculate
End Sub
9. Workbook DoubleClick Event
This is one of the widely used events in Excel. This happens when you double-click on a specific cell. After this event, that cell will change its font and background color along with the font type.
Private Sub Worksheet_BeforeDoubleClick(ByVal Trg As Range, Cancel As Boolean)
Cancel = True
With Trg
.Interior.Color = vbRed
.Font.Color = vbBlue
.Font.Bold = True
End With
End Sub
10. Command Button Click Event
The Command Button tool is incorporated into this event. This is triggered when you press the command button holding a certain VBA code.
Private Sub CommandButton1_Click()
Dim BnRet As Variant
BnRet = MsgBox("Are you sure?", vbQuestion Or vbYesNo)
If BnRet = vbNo Then Exit Sub
End Sub
11. Drop Down (Combo Box) Change Event
When a user selects a particular item from the active X drop-down list, he/she can decide the choice using this event and afterward write this code for adapting other items of the sheet accordingly.
Private Sub ComboBox1_Change()
MsgBox "Your selection" & ComboBox1.Text
End Sub
12. Tick Box (Check Box) Click Event
You can also create a Tick or Check Box by running the following code. It will benefit the user to see if any changes happened in the worksheet. The values returned are either True or False based on whether it has been checked or not.
Private Sub CheckBox_Click()
MsgBox CheckBox.Value
End Sub
13. UserForm Activate Event
This event is used to set up a default value in an Excel form. For example, a default institution name in the name text box.
Private Sub UserForm_Activate()
TextBox.Text = "Institution Name"
End Sub
14. Change Event
In this event, there will be controls on the Excel form for any kind of changes that occur. For instance, this code puts a restriction on the length of names being entered.
Private Sub TextBox_Change()
If Len(TextBox.Text) > 50 Then
MsgBox "The object is restricted to 50 characters", vbCritical
TextBox.Text = ""
End If
End Sub
15. Click Event
This event is triggered when it’s your wish to control the form. Here, an OK button would place a certain value on the spreadsheet for future reference.
Private Sub CommandButton_Click()
ActiveSheet.Range("B5").Value = TextBox.Text
Me.Hide
End Sub
16. Application.OnTime
This event is different from the events that we discussed so far. Because it is stored within a regular VBA mode, not in a specific object. The code runs when it is generated at a specified time.
Sub NotificationTime()
Application.OnTime TimeValue("22:00:00"), "ShowNotification"
End Sub
Sub ShowNotification()
MsgBox "Lunch Time"
End Sub
17. Application.OnKey
Lastly, the Application.OnKey event happens when you try to use a keystroke on your Excel file.
Sub TestKey()
Application.OnKey "g", "TestKey"
End Sub
Sub TestKey()
MsgBox "You just pressed the key 'g'"
End Sub
Read More: How to Use VBA OnKey Event in Excel
How to Disable VBA Events in Excel
If you want to turn off any existing event, apply this code to stop it. All you need to do is to incorporate this in disabling the event and re-enable it at the end of the code. Remember that it runs across the whole workbook, not any specific worksheet.
Sub DisableVBAEvents()
Application.EnableEvents = False
Application.EnableEvents = True
End Sub
Download Practice Workbook
Get this sample file and try it yourself.
Conclusion
So, this was all from me on Excel VBA events with a complete guideline. Let me know in case you face any issues while using this article.
