VBA Event Handling: Excel Workbook Events

Use VBA ThisWorkbook event handling to capture a variety of application-level events. The events captured in the VBA ThisWorkbook module can be used to trigger unique actions based on a user’s interaction with Excel.

In this context, an event is an action or occurrence recognized by Excel that may be handled by it. This will make more sense in a few minutes.

Excel events can either be triggered by Excel, by the user or by third-party applications. An event handler, therefore, is a callback subroutine that handles inputs pertaining to an event in a program. This, in turn, allows the programmer to check for certain desired states before either allowing, halting or altering further code execution and/or other events.

Don’t worry if this all sounds a bit abstract and technical to you at this stage. You’ll have a very clear understanding of what Excel events and Excel event handlers are when you’re finished reading this tutorial.

• Typical event handler format
  
• Setting up a Workbook event handler
  
• List of workbook event handlers
  
• Workbook Event Handling Examples
  
• Excel Workbook Event Cautions
  

VBA events and their corresponding event handlers can be grouped into three main categories:

• Workbook events. These events pertain to all the worksheets in the workbook and the objects embedded in them such as charts, pivot tables, etc. This are typically handled using the ThisWorkbook module in your VBA editor.
• Worksheet events. These events pertain to a particular worksheet and all the objects embedded in it such as charts, pivot tables, etc.
• Userform events. Userform events only pertain to a particular userform (a form you make from the VBA editor) and the objects embedded in it.

Excel VBA is normally characterized as an event-driven language, as it comes with a huge range of built-in objects and controls capable of handling events. We’ll focus solely on Workbook event handlers in this tutorial, which are handled via the ThisWorkbook module in your VBA editor. We may write about other types of VBA events in future tutorials. Let us know in the comments if you want to hear more about other VBA event handlers!

Let’s start by outlining what a generic VBA event handler (callback subroutine) looks like and how it works!

Typical event handler format

The code below is pseudo-code and only serves to illustrate how a VBA event handler (event callback subroutine) is structured and works:

Private Sub PseudoEventHandler(ByVal Param1 as SomeDataType, ByVal Param2 As SomeOtherDataType )
    '******************************
    ' Insert your code here
    '******************************
End Sub

On the surface, this looks like any other type of subroutine that takes parameters and allows you to execute your own code within it, but it’s not! Since event handlers are predefined by Excel, they only work when certain conditions are made.

If you do not specify the parameters exactly as Excel expects, you’ll get an error message (like the one below) either when the subroutine is executed or when you compile your code (with Debug > Compile VBAProject in the Visual Basic Editor):

VBA Workbook event compile error

Secondly, the parameters passed to the subroutine are passed by Excel and not by some other code you’re using.

Thirdly, you cannot change the name of the event handler without breaking it! If you do change the name, Excel won’t know which event you’re trying to handle, and your subroutine will then simply be treated like any other subroutine, rather than as an event handler. Event handler names are predefined by Excel, so they won’t be recognized if you try to make changes to the names.

Fourthly, the (pseudo-)event handler above is declared as a Private sub. Amongst other things, this ensures that it doesn’t appear in the Macros Dialog Box when you type Alt+F8:

Macros Dialog Box

Any subroutine with a parameter won’t appear in the Macros Dialog Box, unless the parameter is an Optional Variant type variable. However, not all event handlers come with parameters, so for consistency they are all declared Private by default.

Fifthly, the location of the event handler matters. Depending on which type of event handler you’re dealing with it should be placed in a module pertaining to the scope of the event it handles. More on this in the next section!

Finally, it should be noted that if a parameter to the event handler is not declared as a ByVal (“by value”) variable, it means you can change its value within the scope of the procedure. Event handlers are no different from ordinary subroutines in this respect, except that altering a non-ByVal or ByRef (“by reference”) parameter typically enables you to control whether a specific Excel event is carried out or not. In VBA the name of this parameter is usually Cancel.

You’ll understand exactly what we mean by this when we step through the event handlers of the Workbook object, but before we get to that, let’s show you how to set up an Excel workbook event handler using VBA.

Setting up a Workbook event handler

Open a Workbook, type Alt+F11 and double click on ThisWorkbook in your Project Explorer (Ctrl+R).

VBA ThisWorkbook Excel Object

Next, click the dropdown list to the right where it says (General) and select Workbook. The (Declarations) dropdown list to the far right will now display all the event handlers of the Application.WorkBook object!

VBA Workbook event handlers dropdown list

It’s important that you do not move the Workbook event handling procedures out of this module as they won’t work if you do so. This is what we meant earlier when we said that all event handling procedures must be placed in modules pertaining to the scope of the events they handle. Workbook events must be placed in the ThisWorkbook module

You’re now ready to start working with all the Excel Workbook events. We’ll give you a rundown of all the Excel workbook event handlers in the next section, each of which can be used to trigger actions in your VBA macros.

List of workbook event handlers

Below is a complete list of Workbook event handlers in Excel. You can use it later as a reference when you’re coding your own event handling procedures, but for now a more cursory reading will do. I encourage you to browse through the list, stop whenever you find a procedure that interests you and then read the info on that specific workbook event.

Some of these event handlers are somewhat esoteric and only very rarely used, whereas others are much more common. In the next section, we’ll present some code examples for a couple of the more commonly used event handlers.

Workbook Event Handling Examples

Paste the code below into the ThisWorkbook code module:

' (1) display a message box with the name of the sheet and 
'     the cell address of column A in which a selection change was made
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Sh.Range("A1:A10"), Target) Is Nothing Then
        MsgBox "A new selection was made on " & Sh.Name & ", column A at " & Target.Address
    End If
End Sub

' (2) display a message box with the name of the sheet on
'     which a calculation was performed
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "A calculation was performed on " & Sh.Name
End Sub

The event handlers above become active immediately after you paste the code into the module, provided Application.EnableEvents is set to True.

Test the first by selecting a cell in range "A1:A10" in column A on any of the worksheets in your workbook.

Anyway, once a new cell is selected in the target range, a message like the one below should then appear:

VBA Selection change message

If it doesn’t, try typing Application.EnableEvents = True in the Immediate window of the Visual Basic Editor and then press Enter.

In the Workbook_SheetSelectionChange event handling procedure above, we’ve narrowed down the handled worksheet area (cell range) with the following piece of code: If Not Intersect(Sh.Range("A1:A10"), Target) Is Nothing Then

This means the code inside the conditional statement above is only executed if range "A1:A10" of the active sheet and the range of the cell in which the selection change was made intersect or “overlap.” In other words, the message box will only appear if the selection change was made in range "A1:A10". A similar code can be placed in many of the other event handler macros to restrict your macro from running unless certain cells are triggered.

In the second code example, we check for calculations performed on any sheet in the workbook. For instance, try entering a simple formula such as “=2+3” into any cell of your workbook and press enter. A message like the one below should now appear:

VBA calculation performed message

Since the built-in Excel event handler only returns the corresponding sheet object, there is no easy way to determine in which range(s) of the worksheet the calculation(s) occurred, so we simply display a message with the name of the worksheet instead.

Excel Workbook Event Cautions

Even though event handling is a wonderful thing which can make your life a lot easier, improve the user experience and make your applications much more powerful, some words of caution are in order.

• Use precise coding. Event handlers can lead to a sharp decline in performance, especially if you’re not careful about how you code them! Be as specific as possible when you use them so that they’re only triggered exactly when you need them to be. In other words, you should strive to achieve the goals of your coding with as little code execution as possible!
• Use the less expensive event handler. If several event handlers can achieve the same goal, you should go for the one who takes the least toll on your system. Don’t use Workbook_SheetSelectionChange if what you really want is to monitor selection changes on only one of your worksheets! In that case, you should use the Worksheet_SelectionChange event handler instead.
• Turn off event handling when you’re running procedures. There exist valid exceptions to this rule, but you should adhere to it whenever you do not need event handling while your procedure is running. If your procedure triggers event handlers while it’s being executed, the execution time will be considerably longer than it needs to be. Simply set Application.EnableEvents = False at beginning of the procedure and then set it back to True and the end of it.
• Is event handling the right solution for you? Sometimes the benefits of using event handlers are outweighed by the cost of using them. Workbook event handlers are particularly expensive since they work at the application level and typically monitor many Excel objects at once, rather just a single object, such as a worksheet. If your workbook is slow to work with for no immediately apparent reason, the problem could be that laborious and unnecessary event handling procedures are running in the background. Try turning them off and see if you can spot any performance differences.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.