How to use Events in Excel VBA
Events are a common thing in computer programming and it allows the programmer to do very interesting things, to make the application more user-friendly and more effective. VBA in Excel allows any programmer to use events in a very simple way. In order to make great user interfaces in your VBA applications and macros, you absolutely need to know about events in Excel VBA!
What are events
An event is when the user (or Excel itself) does a specific action in Excel (for example: click on a cell, open a worksheet, save a workbook, refresh a pivot table…). A VBA programmer can easily write some code that will execute when an event occurs. For example, when the workbook is being opened, a message could pop-up and say “Hello” to the user.
This is especially useful to enhance productivity. For example when you use a bunch of Excel files regularly for some reporting, all the files could be opened all at once when you open the main, master file, and pop-up a message box saying something nice to please the user. This example is written below:
MsgBox "Wow you look abnormally amazing today!", vbOKOnly
‘ This to make the user feel happy and improve his/her work efficiency ;)
Note that the procedure name has to be workbook_open in this case, this will be recognized by VBA as the procedure that should run when the workbook opens.
How to use VBA Excel events
VBA Excel events are used quite differently from the way it works in other programming languages in general. The good news is : it is simpler, but a significant draw-back is that you lose some flexibility in the process.
First, VBA events have to be set in specific places that depend on their area of focus. You can’t define events in modules.
- Events regarding cells and worksheets are defined in the corresponding worksheet object,
- Events regarding the whole workbook are defined in the "ThisWorkbook" object,
- Events about forms are defined in the worksheet that contains it or in the relevant form object and so on…
Second, VBA events can only be set using a specific sub name. That is, you have to know the standard name of that event’s sub to use it, but hey, don’t worry, because I am nice to you, I made a shortlist of available events below.
So to execute some code when the workbook opens, you have to put the code in a sub called workbook_open() in the ThisWorkbook object. Once this is clear you can go to the next part!
What are the typical events to use in VBA Excel
Workbook VBA events
Regarding the workbook object here are several useful events:
- Workbook_open() : executes some code just after this Excel file has opened
- Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean) : executes when the user tries to save the file (before it is saved). Notice that the sub takes two Boolean (True or False) arguments. The second one called “Cancel” is set to False by default, but if you set it to True before the end of the sub, then the saving is cancelled, the file won't be saved.
- Workbook_AfterSave(ByVal Success As Boolean) : executes after the file is saved (so after the previous event). The Success variable contains True if the file was saved successfully. Note that you can always set the Success variable, it won’t prevent the file from saving and it won’t have any effect. It is a purely informative variable.
- Workbook_BeforeClose(Cancel as Boolean) : The event runs just before the workbook is closed. Like the previous event in this list, the sub has a parameter that allows you to Cancel the closing of the file if set to True in the sub. What about creating a file that cannot be closed (at least not easily) to troll your friends? This event is also quite useful to check that the file is correct before it is closed.
- Workbook_NewSheet(ByVal Sh As Object) : The event triggers when a new sheet is created and the Sh object contains the new sheet.
Example to rename a sheet in a specific way when it is created:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "Cool sheet number " & Sh.Index
- Workbook_SheetActivate(ByVal Sh As Object) : works very much the same, triggers when a sheet is activated and returns the corresponding worksheet object.
- Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) : this event triggers when a pivot table is updated in a sheet, and returns the corresponding worksheet and PivotTable objects
- Workbook_SheetCalculate(ByVal Sh As Object) : this event triggers when a sheet is computing and the Sh variable returns the worksheet object where the computation was done.
- Workbook_BeforePrint(Cancel As Boolean) : you guessed it! This event triggers when you want to print something, after the print window opens but before the printing is done, and it allows you to cancel thanks to the variable parameter.
- Workbook_WindowResize(ByVal Wn As Window) : this one triggers when you resize the window in the Excel application were the worksheet is open
Worksheets VBA events
There are also events that apply only to a sheet (you need to put the event code in a worksheet object):
- Worksheet_Calculate() : this is triggered when the worksheet containing this event definition is calculated,
- Worksheet_Change(ByVal Target As Range) : triggers when a cell is modified in this worksheet, and the cell is a parameter in the sub,
- Worksheet_FollowHyperlink(ByVal Target As Hyperlink): triggers when the user clicks on a link,
- Worksheet_SelectionChange(ByVal Target As Range): triggers when the user changes the current selection in the sheet.
The main takeaways
These are only a tiny sample of the full array of available events in Excel. However there are important takeaways from this list:
- Worksheets and Workbook share identical events (click, calculate, activate, pivot table events…), but the events defined in a worksheet only apply to this single sheet and not to the whole workbook.
- Some events allow modifying the behavior of Excel (BeforePrint, BeforeSave, BeforeClose…), which make them powerful but that need to be used cautiously, unless you want your application to become a torture engine for the user.
- Events can trigger each other, and you should be careful not to have too many events in a row as it can hurt performance and make it hard for the user to understand what is happening. People are used to normal Excel behavior and one should not deviate too much from it, or the users will be disoriented.
- As a good practice to enhance usability of your application, you should always avoid long VBA processes happening because of an event. This process wasn’t necessarily expected by the user, and he may not know what happens. A very bad thing would be to have an infinite loop of events.
There are many more events available. The good thing is you can access the event and generate the code very easily through VBA! No need to learn all the events by heart! As I want to check that you got the idea of this article before I disclose my secrets to you, I will ask you to do the quiz on this article first and if you do well enough, you can access it! This seems fair! You will find below or on the left the button with a "play" image to do it. (Don’t worry, no trick, I have nothing to sell)