Trapping for Worksheet Events

When writing VB code associated with a spreadsheet, it is common to add a button to the spreadsheet that the user pushes to execute the VB code when the desired changes have been made to the controls and the values have been entered in the cells.  For example, the following code performs some simple tests to see if someone is ready to take the Professional Engineer test:

In this case, we don't need any code for the click events for the option and checkbox controls.  We simply need to add the following code for the "Do I Qualify" button:

This code works fine, but why require the user to click on the button?  Why not set up the spreadsheet so that anytime the user clicks on a control or changes the value of a cell, the VB code is automatically executed and the results are updated?  This can be easily accomplished using the "Change" event for the worksheet.

Workbooks and Worksheets

Before discussing the Change event, we need to first define a couple of terms.  When the VB compiler for Excel is open, you will see a list of objects in a tree on the left side of the window.  At the bottom of the tree you will see the following objects:

A "Workbook" object represents the entire spreadsheet, including all of the sheets.  If you double click on this object, it will bring up the source code related to the workbook as a whole.  The other objects ("Sheet1", "Sheet2", & "Sheet3").  Double clicking on these objects brings up the code related to these objects.

Once you open the window related to a particular sheet, some important information related to the sheet is displayed at the top of the sheet as follows:

The combo box on the left (the one that is open) lists all of the objects associated with the sheet.  Note that each of the controls on the spreadsheet are listed along with the worksheet itself.  If you highlight one of the objects, you can then select an event from the combo box on the right:

Selecting one of these events creates the subroutine for the selected event.  For example, if I click on the "Change" item, the following code appears:

The Calculate and Change Events

Note that the list of available events for the worksheet include the "Calculate" event and the "Change" event.  By selecting these items, we can then fill in the code for these events.  The resulting code will be executed as follows:

Calculate Event

The Calculate event looks like this:

and is called each time the formulas in the worksheet are recalculated.  Note that you must have at least one formula in your spreadsheet in order for this event to be called.

Change Event

The change event looks like this:

and is called each time any of the cells in the spreadsheet are changed.  Note that the subroutine takes one argument which is the range that has been changed.  If we want the spreadsheet to be updated any time the user enters new data, this is the event we want to use.  First of all, we remove the button so that the spreadsheet looks as follows:

Next, we will modify the code in the Change event to update the spreadsheet.  However, this event is not called when a control is changed, it is only called when a cell is changed.  Therefore, we will first create a subroutine that performs the calculations:

Next, we will modify the Change event so that it calls this subroutine:

Note that we have to temporarily turn off trapping for events prior to updatings the results.  This is because the Update_Results sub changes the value of some of the cells.  This generates a new Change event which brings us right back to this sub.  This results in an infinite loop.  To be safe, you should always turn off event trapping while you make any changes in code.

Finally, to ensure that the click events for the controls cause the results to be updated, we add a call to the click event subroutines for each of the controls as follows:

At this point, clicking on any of the controls, or updating the value in the "years" cell triggers the VB code to update the results.

Checking on the Target

Note that Worksheet Change event sends an argument called Target that represents the range of cells changed.  This could be a single cell or a range of cells.  In some cases, it is useful to check on the range of cells that have been modified.  To do this, you can check on the Target object passed as a parameter to the Change event sub.  Target contains the cell or range of cells changed.  Ideally, you could use a simple statement such as:

If Target = Range("B4") Then

or

If Target <> Range ("B13") Then

To check the value of Target.  However, this will not work because both Target and Range() are objects.  As an object, when you say

If Target = Range("B4") Then

what you are really saying is

"if the value of target is equal to the value of cell B4, then".

This statement would return true if Target corresponded to ANY cell that happened to have the same value as cell B4.  A simple way to solve this problem is to check on the Address property as follows:

If Target.Address = Range("B4").Address Then

In many cases, however, what you really want to know is whether or not Target is a portion of an entire range of cells.  An efficient test for this type of case is to use the Intersect method associated with the Application object.  This method returns the intersection between two ranges.  The idea is to intersect the target range and the range corresponding to the input cells and see if the result is non-empty.  This can be accomplished as follows:

This approach will work with input ranges spanning multiple cells.  For example: