Excel VBA Events (A Complete Guideline)

What Is an Event and Event Handler in Excel VBA?

To understand an Event and Event Handler, we will illustrate with an example.

  • Prepare a dataset according to your preference. We have prepared one with the information of the Order List of 8 customers with Product ID and Price.

What Is an Event and Event Handler in Excel VBA

  • Go to the Developer tab and select Insert.
  • Choose Command Button from the ActiveX Controls section.

  • Right-click on the Command Button and select Properties.

  • Customize the Caption and ForeColor according to your preference.

  • Insert this code and run it.
Private Sub CommandButton1_Click()
    Range("B5").Interior.Color = vbBlue
End Sub

  • You will see the following output.

Excel VBA Events

In this example, Clicking on the Command Button is an Event. The Subprocedure will depend on the code you entered. This Subprocedure is an 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 enter the code related to that object. Following are the five ways to operate it:

Method 1 – In Worksheet Code Window

For worksheet code, here is the process to insert code based on the specific object.

  • Select the worksheet object in your VBA Project.

Where to Insert Event-Related Code in Excel

  • Double-click on it and select Worksheet from the top-left corner of the window.

  • Select any event from the Selection Change list according to your object.

  • Insert your code as per the image below:


Method 2 – In ThisWorkbook Code Window

ThisWorkbook also works like the Worksheet code in the VBA project.

  • Select ThisWorkbook from the worksheet object list.

Where to Insert Event-Related Code in Excel

  • Choose Workbook from the top-left corner panel.

  • Select the type from the options and place your code.


Method 3 – In UserForm Code Window

In case you want to create a UserForm in Excel, you can apply the UserForm code.

  • Right-click on any worksheet object and select UserForm from the Insert section.

Where to Insert Event-Related Code in Excel

  • You will see the new window to insert the UserForm object.


Method 4 – In Chart Code Window

  • Select the Chart Sheet name from the worksheet object list.

Where to Insert Event-Related Code in Excel

  • Select Chart from the drop-down menu.

  • Select any event according to your code and insert your code.


Method 5 – In Class Module

We can insert VBA code in the Class Module from the Insert section in any worksheet object.

Where to Insert Event-Related Code in Excel


Different Types of VBA Events in Excel

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

Different Types of VBA Events in Excel

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

Workbook NewSheet Event

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, followed by saving the workbook in C Drive.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox "Save the File in C Drive"
End Sub

Workbook BeforeSave Event


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

Workbook BeforeClose Event


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

Workbook BeforePrint Event


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

Worksheet Activate Event


7. Worksheet Change Event

This event occurs when you operate a change on your worksheet. However, there are some distinctive situations where this code would be 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

Worksheet Change Event


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

Workbook SelectionChange Event


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

Workbook DoubleClick Event


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

Command Button Click Event


11. Drop Down (Combo Box) Change Event

When a user selects a particular item from the active X drop-down list, the user 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

Drop Down (Combo Box) Change Event


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

Tick Box (Check Box) Click Event


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

UserForm Activate Event


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

Change Event


15. Click Event

This event is triggered when you 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

Click Event


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

Application.OnTime


17. Application.OnKey

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

Application.OnKey

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

How to Disable VBA Events in Excel


Download Practice Workbook


Related Article

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

3 Comments

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo