Excel VBA Events (A Complete Guideline)

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.

Download Practice Workbook

Get this sample file and try it yourself.

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

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

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

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

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. Follow ExcelDemy for more excel related blogs.