In General, the events are nothing but are happening of something. It is the same in excel. But sometimes we want something to happen automatically when a certain event occurs. To do something when a specific event happens in Excel, we use Excel VBA event.
Excel VBA Event Handlers: Types
There are mainly 7 types of Event Handlers in Excel VBA.
Let's explore them one by one.
The application-level events are triggered when the application(Excel) is closed, opened, Activated, protected, unprotected, etc.
There are more than 50 types of events on the Application level. So we can't discuss all of them here.
Scope of the Application Event:
These events will work on all the workbooks of excel, as long as the code containing workbook is open. For example, if you have created an application-level event to tell you the sheet name of the active sheet, then it will be triggered on every sheet activation of any workbook.
How to create an Application event handler in VBA?
The Application event creation is a bit tricky. I have explained it here in detail with example.
Scope of the workbook event
The workbook events work on the entire workbook that contains the code. The event can workbook open, close, activate, deactivate, sheet change, etc.
Where to write Workbook events?
The workbook events are written on the workbook object.
How to write a Workbook event?
Follow these steps:
1. In the project explorer, double click on the workbook object. The code writing area will be displayed. All the workbook scoped events are written here.
2. On the top left of the code writing area, you will see a drop-down menu. Click on the drop-down menu and choose the workbook. By default it is general.
3. Once you choose the workbook from the left drop-down, it will by default insert a workbook_open event subroutine. But if you want to use a different event subroutine, then choose it from the top-right dropdown. It will list all available workbook events.
4. Choose the event you need. For the sake of the example, I choose the SheetActivate event. This event fires on every selection of the sheet in the code containing workbook.
Workbook Event Example: This is a simple example. I just want to show the name of the worksheet that is activated. For that, I simply use the SheetActivate Event in the Workbook object.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name & " Activated"
End Sub
Now, whenever a new sheet on this workbook will be activated, this event will fire. You will be prompted with the massage, sheet name activated.
I know this code is not that useful but you can put any set of instructions in between these lines. You can call the functions and subroutines from the modules itself.
All the range and cell-targeted events are written in the worksheet events. You can read about the worksheet events here.
The scope of the worksheet event
The worksheet events are targeted to the ranges and cells of a specific worksheet. A worksheet event will fire on events that happen on the specific worksheet (The worksheet that contains the code).
Where the worksheet events are written?
The worksheet events are written on the worksheet object.
How to write a worksheet event handling code?
It is the same as the workbook events.
1. In the project explorer, double click on the worksheet object. The code writing area will be displayed for the worksheet. All the worksheet scoped events are written in these worksheets.
2. On the top left of the code writing area, you will see a drop-down menu. Click on the drop-down menu and choose the worksheet. By default it is general.
3. Once you choose the worksheet from the left drop-down, it will by default insert a worksheet_selectionChange event subroutine. But if you want to use a different event subroutine, then choose it from the top-right dropdown. It will list all available worksheet events.
4. Choose the event you need. For the sake of the example, I choose the Worksheet_SelectionChange(ByVal Target As Range) event. This event fires on every change of the selection of a range on the sheet.
Worksheet Event Example
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "You are in " & Target.Address
End Sub
The above event is written in sheet1 of a workbook. This event will show the range address, you have selected on the code containing sheet, whenever you will change the range selection. Below are some more examples of worksheet events.
The worksheet events are mostly used in dynamic dashboards. You can use cells as checkboxes or active selections to make your dashboards dynamic.
Below are some more examples of worksheet events.
Using Worksheet Change Event To Run Macro When any Change is Made
Run Macro If Any Change Made on Sheet in Specified Range
Simplest VBA Code to Highlight Current Row and Column Using
There are two types of chart events in Excel. One is the normally embedded charts that we have discussed here in detail. It is much like application-level events.
Another is the Chart sheet. These are the special sheets that only contain the charts connected to data on some other sheets.
When it comes to events, they are much like normal sheets.
Where to write charts events?
The chart events are written in the chart object. Just double click on the chart sheet to open the code area.
How to write chart events?
Follow these steps:
1. In the project explorer, double click on the chart sheet object to open the code area. All the specific chart sheet related events are written here.
2. On the top-right corner of the code area, you will see the usual drop-down. Select the chart from that drop-down.
3. From the right corner, select the event you want.
For example, if I want to do something as soon as the user selects the chart, I will use the Chart_Activate event.
Example: Chart Sheet Event
Private Sub Chart_Activate()
MsgBox "The Chart is Refreshed"
End Sub
The above piece of code will fire as soon as you will select the chart sheet. Here, it will just show the message that the chart is refreshed but you can do a lot. Like you can dynamically select the data range for the chart before you show this message.
Below are some more examples of chart events:
The user form event is just like other events. There are several events that occur on the user form. You can use those events to trigger the events.
Where to write the User Form events?
To write a user form event, you first need to insert a UserForm.
1. Then right-click on the UserForm and click on the view code. Now the code area will open.
2. Now on the top-left drop, select the Userform.
3. From the left drop-down, select the event you want to use to trigger the code execution.
4. Write the code you want in between the code event code.
The below example simply shows the message when a user form is activated.
Private Sub UserForm_Activate() MsgBox "Hi there, Please double verify your information." End Sub
The above code just shows a message, but you can use this event to pre-fill the form with some default inputs or use sheet information to fill it.
These events are triggered when a specified key or key combination is pressed. It is much like creating yours on shortcuts.
The OnKey event is actually a function or method of Application class that has two arguments as shown bellow:
Application.onkey Key, ["procedure"]
The key is the key or key combination that you want to use as the trigger.
"Procedure" is an optional argument that is a string name of the procedure or macro that you want to fire. If you don't define the procedure, it will fire the current procedure.
Where to write the Onkey Events?
Well, you can write the Onkey event on any normal module. They will work in normal modules but first, you will be needed to run that subroutine that contains the Onkey Instructions. It is not like you have run the macro every time to use the Onkey events. Only once you will be needed to run that macro when you open the workbook.
If you don't want to run the macro that contains the Onkey events, then you can put them into the workbook_open() event in the workbook object. It will make the Onkey events active as soon as you open the workbook that contains the Onkey events.
How to write an Onkey Event handler?
So, if you already have some macros that you want to run with a specified shortcut then write a new procedure that will contain the list of the shortcuts. For example, here I have a macro that show's the message the shortcut is working.
Sub show_msg() MsgBox "The shortcut is working" End Sub
Now I want to run this macro as I press the key combination of CTRL+j. To do so, I write the below VBA code.
Sub Activate_Onkey() Application.OnKey "^j", "show_msg" End Sub
"^" (carate) is for CTRL-key. Below is the table for all key abbreviations in excel VBA.
https://docs.microsoft.com/en-us/office/vba/api/excel.application.onkey
How to Activate Onkey Event?
After writing the above code in a module, if you go on the excel view and use the CTRL+J key, it won't work. First, you need to run the sub that defines the OnKey events. So run once the Activate_Onkey() sub and then it will work for the entire session. Once you close the workbook that contains the Onkey definitions, it will stop working.
You can put the Onkey definitions within the procedure that you want to happen. But then you will have to run the macro once manually. That is why I suggest putting the Onkey events in the Workbook_Open events. It will make all Onkey events activate automatically.
As the name suggests, the Onkey event fires the specified subroutine on or after the earliest specified time possible. Excel may be busy in some other tasks like executing the sum set of instructions or being in copy past mode. In that case, it may delay the Ontime event. That is why the argument is shown as the earliest time.
Syntax of OnTime Event
The Ontime event is a function of the Application class. It has two essential arguments and two optional arguments.
Application.Ontime EarliestTime, "Procedure",[LatestTime], [Schedule]
The EarliestTime is the time when you want your procedure to run. But Excel will run the specified macro after the defined earliest time, only once it is free.
The "Procedure" is the name of the procedure that you want to run at the specified time.
As I said that there is no guarantee excel will run your procedure at the specified time. The LastestTime is the time after the earliest time to give Excel a window to be free and execute your task.
If you want to deactivate your scheduled OnTime event then set schedule to false.
Where to write the Ontime Event?
The OnTime event can be written in any module. You will have to execute the event containing the procedure to activate the event.
If you want your event to be activated as soon as you open the workbook that contains the event, then put it in the workbook_open event. It will activate the event as soon as you open the code containing the event in excel.
How to write the Ontime Event?
Let's say you have a subroutine that shows the current date and time
Sub show_msg() MsgBox "The current date & time is " & Now End Sub
Now if you want this procedure to run on after 5 seconds of another macro's run then you will need to put this code.
Sub OnTimeTest()
'--some other tasks
Application.ontime Now + (5 / 24 / 60 / 60), "show_msg"
End Sub
Once you run the OnTimeTest subroutine, after five seconds of its run it will trigger the show_msg subroutine. So it will be good if you want to do something after a few times of doing something else, use the above structure.
If you want your macro to run itself after every few secs/mins/hours/etc then you can call that function itself. It would be a kind of recursive subroutine.
Sub OnTimeTest() MsgBox "The current date & time is " & Now Application.ontime Now + (5 / 24 / 60 / 60), "OnTimeTest" End Sub
The above subroutine will run after each five-second once you start it.
So yeah guys, these are the events in Excel VBA. Some of the above categories have a wast variety of events triggers. Of course, I can't explain all of them here. That will make a book long article. This was just an introduction to the events available in the Excel VBA. For more information follow the links embed throughout the articles. I have mentioned some related articles below. You can read them too.
If you have any doubts related to this article or any other excel/VBA thought, ask us in the comments section below.
Related Articles:
The Worksheet Events in Excel VBA |The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.
Workbook events using VBA in Microsoft Excel | The workbook events work on the entire workbook. Since all the sheets are part of the workbook, these events work on them too.
Prevent an automacro/eventmacro executes using VBA in Microsoft Excel | To prevent the run of auto_open macro use the shift key.
Chart object events using VBA in Microsoft Excel | The Charts are complex objects and there are several components that you attached to them. To make the Chart Events we use the Class module.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
