Calling Subroutines with Worksheet and Workbook Events

Form and ActiveX Controls provide a user-friendly way to initiate Excel macros, but it may not always be clear to the user that a button needs to be clicked. If you find yourself including notes such as “please click button here”, you may want to consider Worksheet and Workbook events to call a macro. Events will automatically call a subroutine when a user completes a specific action, such as changing the value in a cell or opening a workbook.

Overview

This post within the Getting Started with VBA series continues the focus on Functions and Subroutines in VBA.

The previous post on subroutines discussed a number of techniques for calling subroutines, dependent on the user clicking on form controls, shapes, macro functions, or being initiated within a caller subroutine. These techniques rely on the user interacting with menus and objects that are not part of the traditional spreadsheet interface.

VBA provides options for initiating subroutines through interaction with a spreadsheet. While this initially may not seem like a desirable ability, you will quickly realize that this is a required feature for building any sort of robust application in Excel.

Sample File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Worksheet Events

Worksheet Events can eliminate the need for the user to complete a secondary step (such as clicking a button) to activate a macro. Actions like clicking on a cell, changing a cell’s value, or selecting a worksheet can trigger a specific procedure.

Although Worksheet Events are not classified as intrinsic (built-in) procedures–as the underlying procedure must be created by the user–the name of the subroutine is the basis for the trigger. In short, there are specific subroutine names that must be used to activate these events.

To create events, select a Worksheet module from the project explorer.
Select a Worksheet module from the project explorer.

Worksheet events must be placed in a worksheet module (under Microsoft Excel Objects within the Project Explorer) and will only impact the worksheet which is tied to that specific module. Having an event trigger on every worksheet would require it to be duplicated for all worksheet modules.

Change

Let’s say that you created a subroutine that will provide information to the user dependent on the value in a drop-down box. Any time the user changes the value in the drop-down box, the information will need to be refreshed. The most straightforward way from a developer standpoint is to add a Form or ActiveX Control that the user will need to click for every change. However, this is not a user-centric way of refreshing the information — the user may not know that a button needs to be clicked.

The Worksheet_Change event provides us with a subroutine which is triggered (also referred to as fired) when a cell value is changed. In it’s bare bones application, it can be fired off any time any specific cell in the worksheet is modified.

The following is a simple script which will return a message box whenever any cell within a worksheet is changed.

The Worksheet_Change event requires a specific subroutine name. Any modification to the spelling of the subroutine will deactivate the feature. The declaration requires one argument, and should always be the identifier Target. Target is a built-in reference to the current cell selection in the workbook and must be declared as a Range object.

The above example of the Worksheet_Change event is a bit broad, as you may not want to fire off a script if any cell within a worksheet changes. In many situations, you may want a change event to fire off if a specific cell is changed. A conditional statement can be used to test if a specific cell is changed, such as follows:

In the following example, the Intersect() function is invoked to fire the change event anytime a cell within a range is changed.

This approach also works with named ranges. Simply replacing the address between quotes with the range name will fire off the event whenever a cell is changed within that named range.

Each worksheet can only have one Worksheet_Change subroutine, but multiple conditionals (or other procedures and scripts) can be included within the procedure. The following combines the previous two examples into one Worksheet_Change subroutine.

Finally, it’s important to remember that the Worksheet_Change event will only work in the Worksheet module where it is declared. That being said, if this procedure exists in Sheet1, it will not work in Sheet2. For the same routine to work in Sheet2, the script must be duplicated and placed within the Sheet2 module.

Common Applications of a Change Event

  • Initiating a data validation script.
  • Modifying data in a dashboard when a user makes a selection from a dropdown list.
  • Auto-completing parts of a form dependent on preceding data.
  • Reformatting data entered (e.g. changing a phone number to a proper format).

The Worksheet_Change event was used in our case study: A Financial Statement Template for April Fools Day.

SelectionChange

The Worksheet_SelectionChange event is nearly identical to the Worksheet_Change event in syntax, but the behavior is quite different. Instead of firing a procedure after a cell value is changed, the procedure will be fired immediately upon clicking on the cell.

In its most simple form, the following will return a message box when any cell within a worksheet is clicked.

Also identical to the previous Worksheet_Change event: the subroutine title is specific and should not be changed. The behavior of the Target reference is also the same.

The following will fire when a specific cell is clicked.

And the following will fire if a cell within a specific range or named range is clicked.

Also identical to the Worksheet_Change event, the Worksheet_ChangeSelection event can only be declared once, but can contain multiple conditionals.

Common Applications of a SelectionChange Event

Activate and Deactivate

Two other common worksheet declarations are the Worksheet_Activate and Worksheet_Deactivate subroutines.

The Worksheet_Activate subroutine will fire a procedure when a worksheet initially comes into active state. This means that any time a worksheet is selected and activated, whether through the tabs on the worksheet tab toolbar, a hyperlink, or the activation through a macro, the procedure will run. The following is a simple example which provides a message box whenever the worksheet is activated.

Unlike the previous two change events, Worksheet_Activate does not require any arguments to be passed. The syntax for the Worksheet_Deactivate event is identical, except for the subroutine title. The following will present a message box whenever a user activates a worksheet outside of the one containing this subroutine (moved from the current worksheet).

Similar to the change events, the spelling of the declarations cannot vary, and they are only relevant to the specific worksheet module which contains them. The subroutines would need to be duplicated to the other worksheet modules if the desire is to have them work across many worksheets.

The Worksheet_Activate and Worksheet_Deactivate events will not fire under the following circumstances:

  • The Excel window (as a whole application) is brought into or out of focus.
  • A subroutine changes the value of a cell without activating the worksheet.
  • A cell value changes resulting from an Excel formula.

    Common Applications of the Activate and Deactivate Event

  • Updating data through VBA on a just-activated worksheet.
  • Saving the workbook.
  • Running a script dependent on a specific worksheet’s activation or deactivation.

Other Worksheet Events

A number of other Worksheet change events exist to fire off procedures when the users take various actions. Here’s a quick summary of others which may be useful:

  • Worksheet_BeforeDelete: Run procedure after use initiates a command to delete a worksheet.
  • Worksheet_BeforeDoubleClick: Run procedure immediately after user double clicks on a worksheet cell or element.
  • Worksheet_BeforeRightClick: Run procedure immediately after user right clicks.
  • Worksheet_Calculate: Run procedure any time the worksheet needs to recalculate cells. This will typically include the modification of any cell tied to an equation or application formula.
  • Worksheet_FollowHyperlink: Run procedure whenever a hyperlink is clicked.
  • Worksheet_TableUpdate: Run procedure whenever a table is updated.

There are also a number of pivot table events which can be easily accessed. If you add an ActiveX control to your worksheet, you will also have access to many events associated with that control, such as whenever a value is changed or a control goes into or out of focus.

Workbook Events

An additional library of built-in subroutines is available as a workbook event. As opposed to worksheet events–which are dependent on a user’s interaction with worksheet elements and properties–workbook events are dependent on the user’s interaction with the Excel application, including all spreadsheets, ranges, menus, etc. This includes actions such as opening a workbook, printing, or saving. Generally, this can be thought of as any windows toolbar related action.

To create events, select a worksheet module from the project explorer.
Select a worksheet module from the project explorer.

Workbook events must be placed in the workbook module (Microsoft Excel Objects) and will only impact the workbook in which the procedures are contained.

Open

The Workbook_Open subroutine will fire a procedure immediately after the workbook is opened. This is useful for resetting the state of a workbook or making an update before a user can interact with it.

The following procedure will present a message box immediately after a workbook is opened.

Similar to its workbook events counterpart, only one subroutine declaration can exist for each event.

Common Applications of the Workbook_Open Event

  • Generate an introductory message.
  • Reset the state of a the file (e.g. hide Worksheets, cells, etc).
  • Create a proprietary password system.

BeforeClose

The Workbook_BeforeClose event will run a procedure before a workbook is closed.

If the workbook has any unsaved data, the usual “Want to save your changes to…” message box after the BeforeClose script runs. If the user cancels this prompt, the workbook will not close.

Common Applications of the Workbook_BeforeClose

  • Present an exit message to the user.
  • Write data to an external file.
  • Force a workbook save event (using ThisWorkbook.Save). This will always prevent the “Want to save your changes to…” prompt from appearing in all instances.

Other Workbook Events

  • Workbook_WindowActivate: Run procedure when workbook becomes the active workbook. This occurs whenever a workbook is selected while focused on another application, or on a different excel workbook.
  • Workbook_WindowDeactivate: Run procedure when workbook becomes inactive. This occurs whenever another application or another workbook is selected, moving away from the workbook containing this command.
  • Workbook_SheetBeforeDelete: Run procedure immediately before a worksheet is deleted.
  • Workbook_BeforeSave: Run procedure immediately before a save.
  • Workbook_SheetChange: Run procedure whenever any cell within the workbook is changed. Two parameters passed (Sh and Target) permit the use of conditionals. This subroutine is a good alternative to the Worksheet_Change event if you intend to have it impact the entire Workbook, not just a specific sheet.
  • Workbook_SheetSelectionChange: Run procedure whenever any cell within the workbook is selected. Two parameters passed (Sh and Target) permit the use of conditionals. This subroutine is a good alternative to the Worksheet_SelectionChange event if you intend to have it impact the entire Workbook, not just a specific sheet.

Techniques for Organizing Code

While there is no explicit rule against including complex procedures within Worksheet modules, this author’s opinion is to place a call to a normal VBA module when running events, especially if a procedure is complicated. Use the Worksheet and Workbook modules to handle events only, and leave the complicated stuff in the modules. This can help organize code and make it more readable. Here’s a suggested approach:

  • Keep any scripting within the event procedures simple and basic.
  • Anything that requires more than 10-15 lines of code should be moved to a regular module and called from the event procedure.
  • Code within an event procedure should be limited to conditionals which determine which callee subroutine should be called.

More information on calling subroutines can be found in the previous post of the series: Getting the Most Out of Subroutines in VBA.

Enabling/Disabling Events

If you use any Worksheet or Workbook events, it’s important to keep in mind that any macro impacting an element tied to the event (such as changing a cell value tied to a Worksheet_Change event) will trigger said event. While this may be intended at times, when it is not intended, it can lead to undesirable results at most or unnecessary systems overhead and wait times.

If activating an event script is not your intention, Workbook and Worksheet events can be suppressed using the Application.EnableEvents method. Prior to running any script, events can be suppressed by assigning “False” to EnableEvents. Upon completion of the script, “True” should be assigned permitting return to the normal state.

Other Notes

  • Use caution with Workbook_Open events. The user must activate macros before any open events can fire. With newer versions of Excel, users will be able to navigate through and modify worksheets before activating macros.
  • Use caution with Workbook_Close events. These events will only fire off if the user closes a workbook through the menu. If power is cut to the computer, or if Excel crashes, the event will not fire.
  • Intellisense will now help complete the name of an event subroutine — it must be manually typed exactly to VBA specifications. However, upon entering the first event subroutine in a Worksheet or Workbook module, a list of available events will appear in the Procedures drop-down menu.

Leave a Reply