Excel VBA Events (A Complete Guideline) - ExcelDemy (2024)

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.

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

• 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 = vbBlueEnd Sub

• You will see the following output.

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.

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

• 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 Insertsection.

• You will see the new window to insert the UserFormobject.

Method 4 – In Chart Code Window

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

• Select Chart from the drop-downmenu.

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

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

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 = FalseIf Application.UserName <> "Maria" Then Obj.DeleteEnd IfApplication.DisplayAlerts = TrueEnd 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, 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

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 WorksheetFor Each WkSh In ThisWorkbook.WorksheetsWkSh.ProtectNext WkShEnd 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").SelectEnd 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 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.AddressEnd 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.CalculateEnd 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 = TrueWith Trg.Interior.Color = vbRed.Font.Color = vbBlue.Font.Bold = TrueEnd WithEnd 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 VariantBnRet = MsgBox("Are you sure?", vbQuestion Or vbYesNo)If BnRet = vbNo Then Exit SubEnd Sub

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.TextEnd 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.ValueEnd 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 IfEnd Sub

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.TextMe.HideEnd 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 SubSub ShowNotification()MsgBox "Lunch Time"End Sub

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 SubSub 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 = FalseApplication.EnableEvents = TrueEnd Sub

Download Practice Workbook

Related Article

• How to Create Calendar Using VBA in Excel