Automatic procedures and events in Excel VBA (2023)

In this chapter

Know the types of events that can trigger execution. Determine where to put the event-handling VBA code. Run a macro when a workbook is opened or closed. Run a macro when a workbook or worksheet is activated. One way is to let Sub run automatically. In this chapter, I'll go over the ins and outs of this potentially useful feature and explain how to set things up so that a macro runs automatically when a certain event occurs. (No, this chapter is not about the death penalty.)

Preparation for the big event

What events am I talking about here? Good question. An event is basically something that happens in Excel. The following are some examples of the types of events Excel can handle:
A book opens or closes.
A window will be activated.
A table is activated or deactivated.
Data is entered into a cell or the cell is edited.
A workbook is saved.
A worksheet is calculated.
On an object, e.g. B. a button is clicked.
A specific key or key combination is pressed.
A cell is double-clicked.
It happens at a certain time of day.
An error occurs.
Most Excel programmers don't need to worry about most of the events on this list. However, you should at least know that these events exist because they might be useful one day. In this chapter, I discuss the most commonly used events. For the sake of simplicity, I'm talking about two types of events: workbook and worksheet.

Table 11-1 lists most events related to the workbook. You can access the full list by following these instructions:

1. Select the This Workbook object in the project pane.
2. View the code window.
To do this, choose Display code or press F7.
3. Select the workbook object from the object drop-down list (upper left of the code window).
4. Expand the Procedure drop-down list (upper right of the code window).

Table 11-2 lists most worksheet events. These events can be accessed by following these instructions:

1. Select a sheet object in the project window.
2. View the code window.
3. Select the worksheet object from the object list (at the top of the code window).
4. Expand the Procedure drop-down list.

Are the events useful?

At this point, you might be wondering how useful these events can be. Here's a quick example.
Suppose you have a workbook that other people use to enter data. Any value entered must be greater than 1000. You can write a simple macro that Excel will run every time someone enters data in a cell. (The data entry is an event). If the user enters a value less than 1000, the macro displays a dialog box berating the user.
The Data Validation command in Excel provides another way to perform this type of data entry validation, even without using VBA. However, as you will see later in this chapter (see “An Example of Data Validation”), there are some distinct benefits to using VBA for data validation.
This is just one example of how you can use an event. Read on to see more examples.

Scheduling event handler procedures

A VBA procedure that runs in response to an event is called an event handler procedure. They are always Sub procedures (as opposed to Function procedures). Writing these event handlers is relatively easy once you understand how the process works. It all boils down to a few steps that I will explain later:
1. Identify the event for which you want to trigger the procedure.
2. Press Alt+F11 to activate the Visual Basic Editor.
3. In the VBE project window, double-click the appropriate object listed under Microsoft Excel Objects.
For workbook-related events, the object is This Workbook. For a workbook-related event, the object is a worksheet object (e.g., Sheet1).
4. In the object code window, enter the event handler procedure that will be executed when the event occurs.
This procedure has a special name that identifies it as an event handler procedure.
These steps will become clearer as the chapter progresses. Trust me.

Where does the VBA code go?

It is very important to understand where the event handler procedures go. You must be in the code window of an object module. They just don't work if you put them in a standard VBA module.
Figure 11-1 shows the VBE window with a project displayed in the project pane. (For information about VBE, see Chapter 3.) Note that the project consists of several objects:
An object for each sheet in the book (three Sheet objects in this case)
An object named This Workbook
A VBA module that I manually inserted using the Insert Module command

Figure 11-1:
The VBE window displays items for a single project.
Double-clicking on one of these objects will display the code associated with the item, if any.
Event handler procedures that you write go to the This Workbook item (for workbook-related events) or to one of the Sheet objects (for worksheet-related events) in the code window.

Write an event handler procedure

The VBE helps you when you're ready to write an event-handling procedure; displays a list of all events that Excel can recognize.
Figure 11-2 shows a code window for the This Workbook object (the code window is maximized to fill the entire area of ​​the code window). To display this blank code window, double-click the This Workbook object in the project pane. This code window has two drop down lists at the top.

Figure 11-2:
An empty code window for the This Book object.
By default, the Object (left) drop-down list in the code window displays General. To write an event handler procedure, you must select Workbook from the Object drop-down list. (The workbook is the only other item in the list.) If the event handler is for a sheet, double-click the appropriate sheet item in the project pane before selecting Sheet from the Object drop-down list.
Figure 11-3 shows the drop-down list on the right, which consists of all workbook-related events that Excel recognizes. When you select an event from the list, the VBE automatically starts creating an event handler procedure for you. (When you first selected the workbook from the list of objects, the VBE assumed that you wanted to create an event handler procedure for the Open event and created it. You can see this in Figure 11-3 see.)

Figure 11-3:
The drop-down list shows all events related to the workbook.
However, VBE's help only goes so far. Write the Sub statement and the End Sub statement. It's up to you to write the VBA code between these two statements.
Some event-handling procedures accept one or more arguments in the Sub statement. For example, when you select Activate Sheet from the event list for a workbook object, the VBE writes the following sub-statement:

In this case, Sh is the argument passed to the procedure and is a variable that represents the worksheet in the activated workbook. The examples in this chapter make this point clear.

introductory examples

In this section, I'll give some examples to help you understand this event handling business.

Das Open for a Book-Event

One of the most commonly used events is the Open Workbook event. Suppose you have a workbook that you use every day. The Workbook_Open procedure in this example runs each time the workbook is opened. EITHER
the procedure checks the day of the week; If it's Friday, the code will display a reminder message for you.
To create the handler that runs each time the Open Workbook event occurs, complete the following steps:
1. Open the workbook.
Any workbook will do.
2. Press Alt+F11 to activate the VBE.
3. Locate the workbook in the project window.
4. Double-click the project name to view its items, if necessary.
5. Double-click the This Book item.
The VBE displays an empty code window for the This Book object.
6. In the code window, select Workbook from the Object (left) drop-down list.
The VBE inserts start and end statements for a Workbook_Open procedure.
7. Enter the following statements:

The code window should now resemble Figure 11-4.

Figure 11-4:
The event handler procedure runs when the workbook is opened.
Workbook_Open runs automatically every time the workbook is opened. Use the VBA function WeekDay to determine the day of the week. If it is Friday (Day 6), a message box will remind the user to back up files weekly. If it's not Friday, nothing happens.
If today is not a Friday, you may have trouble trying this procedure. Here you have the opportunity to test your own knowledge of VBA. You can change this procedure as you like. For example, the following version shows a message each time the book is opened. It gets boring after a while, trust me.

A Workbook_Open procedure can do almost anything. These event handlers are commonly used to:

Show welcome messages (like Frank's) Open other workbooks
Activate a specific sheet in the workbook. Configure custom menus. Show or hide toolbars
Note that if the user disables macros when the workbook is opened, the event handler procedures will not run. In other words, you can't rely on your event-handling procedures to work every time.

The Before Close event for a workbook

This is an example of the Workbook_Before Close event handler procedure that runs automatically just before the workbook is closed. This procedure is in the code window of a This Book object:

This routine uses a message box to ask the user if they want to save the workbook. If so, the code uses the Save a Copy As method to save a backup copy of the file to drive F. If you adapt this procedure for your own use, you may need to change the drive and path.
Excel programmers often use a Workbook_Before Close procedure to clean up after itself. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (e.g. hide the status bar), it is appropriate to reset the settings to their original state when you close the workbook. You can perform this electronic cleanup with a Workbook_Before Close procedure.

The Before Save event for a workbook

The Before Save event, as the name suggests, is raised before a workbook is saved. This event occurs when you use the Save File or Save File As command.
The following procedure, pasted into the code window for a This Book object, demonstrates the Before Save event. The routine updates the value in a cell (cell A1 on Sheet1) each time the workbook is saved. In other words, cell A1 acts as a counter to keep track of the number of times the file has been saved.

Note that the Workbook_Before Save procedure has two arguments, Save As UI and Cancel. To illustrate how these arguments work, consider the following macro, which runs before the workbook is saved. This procedure prevents the user from saving the workbook with a different name. When the user selects the Save File As command, the UI Save As argument is true.
When the code runs, it checks the Save As UI value. If this variable is True, the procedure displays a message and sets Cancel to True, which aborts the save operation.

Examples of activation events

Another category of events is turning objects on and off, especially sheets and windows.

Enable and disable events in a table

Excel can detect when a particular worksheet is activated or deactivated and run a macro when one of these events occurs. These event handler procedures go to the code window of a leaf object.
The following example shows a simple procedure that runs each time a specific worksheet is activated. This code simply displays a message box with the name of the active worksheet:

Here's another example that activates cell A1 every time the table activates:

While the code for these two procedures is as simple as possible, the event handler procedures can be as complex as you like.
The following procedure (stored in the Sheet1 object's code window) uses the Deactivate event to prevent a user from activating other sheets in the workbook. If sheet1 is disabled (ie another sheet is enabled), the user will receive a message and sheet1 will be enabled.

Enabling and disabling events in a workbook

The previous examples use events associated with a spreadsheet. The This Workbook object also handles events that handle worksheet activation and deactivation. The following procedure stored in the code window
for the This Workbook object runs when any worksheet in the workbook is activated. The code displays a message with the name of the enabled table.

The Workbook_Sheet Activate procedure takes the sh argument. Sh is a variable that represents the active sheet object. The message box displays the Sheet object's Name property.
The following example is contained in a Code This Book window. It consists of two event handler procedures. Workbook_Sheet Deactivate is executed when a sheet is deactivated. Stores the disabled worksheet in an object variable. (The Set keyword creates an object variable.) The Workbook_Sheet Activate code checks the type of activated sheet (using the TypeName function). If the sheet is a chart sheet, the user will receive a message and the previous sheet (stored in the Old Sheet variable) will be reactivated. The effect is that users cannot activate a chart sheet (and always go back to the previous sheet if they try).
A workbook with this code is available on the dedicated website.

Book activation events

Excel also recognizes the event that occurs when you activate or deactivate a specific workbook. The following code, contained in the code window of this book object, runs every time the book is activated. The procedure simply maximizes the book window.

The Workbook_Deactivate code shown below runs when a workbook is deactivated. This procedure minimizes the workbook window:

Note that I didn't use Active Window in this code. This is because the workbook is no longer the active window when it is deactivated. So I used This Workbook which refers to the workbook that contains the code.

Other sheet-related events

In the previous section, I presented examples of worksheet activation and deactivation events. In this section, I discuss three additional events that occur in worksheets: double-clicking a cell, right-clicking a cell, and changing a cell.

The Before Double Click event

You can set up a VBA procedure to run when the user double-clicks a cell. In the following example (saved in a Sheet object's code window), double-clicking a cell makes the cell bold (if it's not bold) or non-bold (if it's bold):

The procedure Worksheet_Before Double Click has two arguments: target and cancel. Target represents the cell (a Range object) that was double-clicked. When Cancel is set to True, the default double-click action is not performed.
Notice that I've set the Cancel argument to True. This prevents the default action from taking place. In other words, double-clicking the cell does not put Excel into cell-editing mode.

The Before Right Click event

The Before Right Click event is similar to the Before Double Click event, except that it involves a right click on a cell. The following procedure checks whether the cell you right-clicked contains a numeric value. If yes, the
The code displays the Number Format dialog and sets the Cancel argument to True (which prevents the context menu from appearing normally). If the cell does not contain a numeric value, nothing special happens: the context menu is displayed normally.

Note that the code available on the website for this topic performs an additional check to see if the cell is empty. This is because VBA considers empty cells as numeric.

the change event

The change event occurs whenever any cell in the worksheet is changed. In the example below, the Worksheet_Change procedure effectively prevents a user from entering a non-numeric value in cell A1. This code is stored in the code window of a Sheet object.

The single argument of the Worksheet_Change procedure represents the range that was changed. The first statement checks if the cell address is $A$1. In this case, the code uses the Is Numeric function to determine if the cell contains a numeric value. Otherwise, a message is displayed and the cell value is cleared. Cell A1 is then activated, useful if the cell pointer has moved to another cell after typing. If the change occurs in any cell except A1, nothing happens.

Why not use Excel's data validation command?

You may be familiar with the Data Validation command. This is a useful feature that makes it easy to ensure that only the right type of data is being entered into a given cell or range. While the data validation command is useful, it's definitely not foolproof. To demonstrate this, start with a blank worksheet and do the following:
1. Select the range A1:C12.
2. Choose Data Validation.
3. Set your validation criteria to only accept integers between 1 and 12, as shown in Figure 11-5.

Figure 11-5:
These settings only allow integers between 1 and 12.
Now enter some values ​​in the range A1:C12. Data validation works as it should. But to see it fall apart, try this:
1. Type -1 in any cell outside the validation range (any cell outside of A1:C12).
2. Choose Edit Copy to copy the negative number to the clipboard.
3. Select any cell in the validation area.
4. Choose Edit Paste.
It finds out that the insert operation is allowed. However, if you look a little closer, you'll see that the cell you pasted the negative value into no longer has any validation criteria. Pasting will clear the data validation criteria! The severity of this error depends on your application. In the next section, I describe how to use the Change event to provide better validation.
Pasting will clear the data validation as Excel sees validation as a format for a cell. This means that it ranks in the same order by font size, color, or other similar attributes. When you paste a cell, replace the formats of the destination cell with those of the source cell. Unfortunately, these formats also contain their validation rules.

An example of data validation.

The following procedure shows a better alternative to Excel's data validation command. Ensures that only positive values ​​are entered in the range A1:C12.

A workbook with this code is available on the website for this topic:

The procedure begins by creating an object variable (ValRange) that represents the range to be validated. DataOK is a Boolean variable that is initially set to True. The for-next loop examines each cell in Target (which is the cell or range that changed). I use the Union function to determine if the cell is in ValRange. In this case, an if statement determines whether the cell's value is less than 0. In this case, the content is deleted and DataOK is set to False.
When all cells have been checked, another If statement checks the value of DataOK. When set to False, one or more cells in the changed range are negative. Therefore, the user receives a message. This routine also works when data is copied and pasted into the validation area.

Events not associated with objects

The events discussed earlier in this chapter are associated with either a workbook object or a worksheet object. In this section, I discuss two types of events that aren't associated with objects: time and keystrokes.
Because times and keystrokes are not associated with a specific object, such as a workbook or worksheet, these events are scheduled in a regular VBA module (unlike the other events discussed in this chapter).

the event on time

The OnTime event occurs when a specific time of day occurs. The following example shows how to program Excel to beep and display a message at 3:00 p.m. m.:

In this example, I use the Application object's OnTime method. This method takes two arguments: the time (0.625 or 15:00) and the code to run when the time comes (display an alarm).
This technique is very useful when you tend to get so absorbed in your work that you forget about meetings and appointments. Just set an OnTime event as a reminder.
Most people (including this author) find it difficult to visualize time in terms of Excel's number system. Therefore, you may want to use VBA's time value function to represent time. TimeValue converts a string that looks like a time into a value that Excel can process. The following statement shows an easier way to schedule an event for 3:00 p.m. m.:

If you want to schedule an event relative to the current time, e.g. in 20 minutes, you can use a statement like this:

You can also use the OnTime method to run a VBA procedure on a specific day. You need to make sure your computer is still running and the workbook containing the procedure is open. The following statement runs the Show Alarm at 5:00 PM procedure. m. December 31, 2005:

That particular line of code can be useful for letting you know that you need to go home and get ready for the New Year's Eve celebrations.
The OnTime method has two additional arguments. If you want to use this method, see the online help for complete information.

Keypress Events

As you work, Excel constantly monitors what you type. Because of this, you can configure a keystroke or key combination to perform a procedure.

Here's an example that remaps the PgDn and PgUp keys:

After configuring the OnKey events by running the Setup_OnKey procedure, pressing PgDn scrolls down one line. Pressing Page Up moves it up one line.
Note that keycodes are enclosed in square brackets, not parentheses. See the help system for a complete list of keyboard codes. Search for OnKey.
In this example I use On Error Resume Next to ignore generated errors. For example, if the active cell is in the first row, trying to move up one row will generate an error that can safely be ignored. Also note that the procedures check which worksheet type is active. The routine remaps the PgUp and PgDn keys only when a leaf is the active leaf.

Running the following routine will cancel the OnKey events:

Using an empty string as the second argument to the OnKey method does not cancel the OnKey event. Instead, it causes Excel to simply ignore the keystroke. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key:

Although you can use the OnKey method to assign a key combination to run a macro, you must use the Macro Options dialog box for this task. See Chapter 5 for more details.