So far in various articles, I have discussed about Events and Event handlers. To give you a fresh start, let’s have a look at the Events and Events handler again.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide and Learn Excel VBA Events Completely with 5 Tutorials.
What is an Event and an Event Handler
Let’s start with an example. Say, I have placed a CommandButton in my worksheet using Developer ⇒ Controls ⇒ Insert ⇒ ActiveX Controls ⇒ Command Button like the following figure. Caption and ForeColor properties have been changed using the Properties window for this control.
I want that Command Button will do something when it is clicked. So here Clicking on the Command Button is an Event. What this command button will do when it is clicked depends on the underlying Subprocedure written by you. Say I want that when this command button will be clicked, cell A1 will be decorated with the Blue background color. So I write a code like the following:
Private Sub CommandButton1_Click() Range("A1").Interior.Color = vbBlue End Sub
This Subprocedure is called Event Handler as it executes when someone clicks on the Command Button.
When I click the command button, cell A1 in my worksheet gets blue as its background color. An event happens, and the Event Handler executes.
Types of Excel Events
Excel works with a wide range of events. In this chapter, we’re going to deal with the following types of events:
- Workbook events: I am pretty sure that you know what a workbook is. Who don’t know, the following figure is for them. You’re seeing here total of 13 workbooks; so, every Excel file is a workbook.
Open an Excel file from your existing ones. A workbook has a good number of events. To find out those, open the VB Editor from the Developer tab. Double click on ThisWorkbook in the project window and select Workbook in the code window. Then click Open, you will get all the available events associated with the workbook.
VBA code that you will write for your workbook-related events must be stored in the ThisWorkbook code module.
- Worksheet events: For a particular worksheet there are also a good number of events. See the figure below.
VBA code that you will write for your specific worksheet events must be stored in that particular worksheet code module.
- Events not associated with objects: There exist two important application-level events: OnTime and OnKey. These two work differently from other events.
So, this was all from me on this topic. Let me know in case you face any issues while using this article.
Happy Excelling ☕