Chapter 11
In This Chapter
Knowing the event types that can trigger an execution
Finding out where to place your event-handler VBA code
Executing a macro when a workbook is opened or closed
Executing a macro when a workbook or worksheet is activated
You have a number of ways to execute a VBA Sub procedure. One way is to arrange for the Sub to be executed automatically. In this chapter, I cover the ins and outs of this potentially useful feature, explaining how to set things up so that a macro is executed automatically when a particular event occurs. (No, this chapter is not about capital punishment.)
What types of events am I talking about here? Good question. An event is basically something that happens in Excel. Following are a few examples of the types of events that Excel can recognize:
In this chapter, I discuss the most commonly used events. To simplify things, I talk about two types of events: workbook events and worksheet events.
Table 11-1 lists some useful workbook-related events. If, for some reason, you need to see the complete list of workbook-related events, you'll find it in the Help system.
Table 11-1 Workbook Events
Event |
When It’s Triggered |
Activate |
The workbook is activated. |
BeforeClose |
The workbook is closed. |
BeforePrint |
The workbook is printed. |
BeforeSave |
The workbook is saved. |
Deactivate |
The workbook is deactivated. |
NewSheet |
A new sheet is added to the workbook. |
Open |
The workbook is opened. |
SheetActivate |
A sheet in the workbook is activated. |
SheetBeforeDoubleClick |
A cell in the workbook is double-clicked. |
SheetBeforeRightClick |
A cell in the workbook is right-clicked. |
SheetChange |
A change is made to a cell in the workbook. |
SheetDeactivate |
A sheet in the workbook is deactivated. |
SheetSelectionChange |
The selection is changed. |
WindowActivate |
The workbook window is activated. |
WindowDeactivate |
The workbook window is deactivated. |
Table 11-2 lists some useful worksheet-related events.
Table 11-2 Worksheet Events
Event |
When It’s Triggered |
Activate |
The worksheet is activated. |
BeforeDoubleClick |
A cell in the worksheet is double-clicked. |
BeforeRightClick |
A cell in the worksheet is right-clicked. |
Change |
A change is made to a cell in the worksheet. |
Deactivate |
The worksheet is deactivated. |
SelectionChange |
The selection is changed. |
At this point, you may be wondering how these events can be useful. Here’s a quick example.
Suppose you have a workbook in which you enter values in column A. Your boss, a very compulsive person, tells you that he needs to know exactly when each number was entered. Entering data is an event: a WorksheetChange event. You can write a macro that responds to this event. That macro kicks in whenever the worksheet is changed. If the change was made in column A, the macro puts the date and time in column B, next to the data point that was entered.
In case you're curious, here's what such a macro would look like. Probably a lot simpler than you thought it would be, eh?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1) = Now
End If
End Sub
By the way, macros that respond to events are very picky about where they are stored. For example, this Worksheet_Change macro must be in the Code module for that worksheet. Put it somewhere else, and it won't work. More about this later (see “Where Does the VBA Code Go?”).
A VBA procedure that executes in response to an event is called an event-handler procedure. These are always Sub procedures (as opposed to Function procedures). Writing these event-handlers is relatively straightforward after you understand how the process works.
Creating event-handler procedures boils down to a few steps:
In the VBE Project Window, double-click the appropriate object listed under Microsoft Excel Objects.
For workbook-related events, the object is ThisWorkbook. For a worksheet-related event, the object is a Worksheet object (such as Sheet1).
In the Code window for the object, write the event-handler procedure that is executed when the event occurs.
This procedure will have a special name that identifies it as an event-handler procedure.
These steps become clearer as you progress through the chapter. Trust me.
It’s very important to understand where your event-handler procedures go. They must reside in the Code window of an Object module. They do not go in a standard VBA module. If you put your event-handler procedure in the wrong place, it simply won't work. And you won't see any error messages, either.
Figure 11-1 shows the VBE window with one project displayed in the Project window. (Refer to Chapter 3 for some background on the VBE.) Notice that the VBA project for Book1 is fully expanded and consists of several objects:
Double-clicking any of these objects displays the code associated with the item, if any.
The event-handler procedures that you write go into the Code window for the ThisWorkbook item (for workbook-related events) or one of the Sheet objects (for worksheet-related events).
In Figure 11-1, the Code window for the Sheet1 object is displayed, and it happens to have a single event-handler procedure defined. Notice the two drop-down controls at the top of the Code module? Keep reading to find out why those are useful.
The VBE helps you out when you’re ready to write an event-handler procedure; it displays a list of all events for the selected object.
At the top of each Code window, you find two drop-down lists:
By default, the Object drop-down list in the Code window displays General.
If you're writing an event-handler for the ThisWorkbook object, you need to click ThisWorkbook in the Project window and then choose Workbook from the Object drop-down (it's the only other choice).
If you're writing an event-handler for a Sheet object, you need to click the specific Sheet in the Project window and then choose Worksheet from the Object drop-down list (again, the only other choice).
After you've made your choice from the Object drop-down list, you can choose the event from the Procedure drop-down list. Figure 11-2 shows some of the choices for a workbook-related event.
When you select an event from the list, VBE automatically starts creating an event-handler procedure for you. This is a very useful feature, because it tells you exactly what the proper arguments are.
Here's a little quirk. When you first select Workbook from the Object list, VBE always assumes that you want to create an event-handler procedure for the Open event and creates it for you. If you're actually creating a Workbook_Open procedure, that's fine. But if you're creating a different event-procedure, you need to delete the empty Workbook_Open Sub that was created.
VBE’s help goes only so far, however. It writes the Sub statement and the End Sub statement. Writing the VBA code that goes between these two statements is your job.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
In this case, Sh is the argument passed to the procedure and is a variable that represents the sheet in the activated workbook. Examples in this chapter clarify this point.
In this section, I provide a few examples so that you can get the hang of this event-handling business.
One of the most commonly used events is the Workbook Open event. Assume that you have a workbook that you use every day. The Workbook_Open procedure in this example is executed every time the workbook is opened. The procedure checks the day of the week; if it’s Friday, the code displays a reminder message for you.
To create the procedure that is executed whenever the Workbook Open event occurs, follow these steps:
Open the workbook.
Any workbook will do.
Double-click the ThisWorkbook item.
The VBE displays an empty Code window for the ThisWorkbook object.
In the Code window, select Workbook from the Object (left) drop-down list.
The VBE enters the beginning and ending statements for a Workbook_Open procedure.
Private Sub Workbook_Open()
Dim Msg As String
If Weekday(Now) = 6 Then
Msg = "Today is Friday. Don't forget to "
Msg = Msg & "submit the TPS Report!"
MsgBox Msg
End If
End Sub
The Code window should look like Figure 11-3.
Workbook_Open is executed automatically whenever the workbook is opened. It uses VBA’s WeekDay function to determine the day of the week. If it’s Friday (day 6), a message box reminds the user to submit a report. If it’s not Friday, nothing happens.
If today isn’t Friday, you might have a hard time testing this procedure. You can just change the 6 to correspond to today's actual day number.
And of course, you can modify this procedure any way you like. For example, the following version displays a message every time the workbook is opened. This gets annoying after a while, trust me.
Private Sub Workbook_Open()
Msg = "This is Frank’s cool workbook!"
MsgBox Msg
End Sub
A Workbook_Open procedure can do almost anything. These event-handlers are often used for the following:
Here's a final example of a Workbook_Open procedure that uses the GetSetting and SaveSetting functions to keep track of how many times the workbook has been opened. The SaveSetting function writes a value to the Windows registry, and the GetSetting function retrieves that value (see the Help system for details).
The following example retrieves the count from the registry, increments it, and then saves it back to the registry. It also tells the user the value of Cnt that corresponds to the number of times the workbook has been opened (see Figure 11-4).
Private Sub Workbook_Open()
Dim Cnt As Long
Cnt = GetSetting("MyApp", "Settings", "Open", 0)
Cnt = Cnt + 1
SaveSetting "MyApp", "Settings", "Open", Cnt
MsgBox "This workbook has been opened " & Cnt & " times."
End Sub
Here’s an example of the Workbook_BeforeClose event-handler procedure, which is executed automatically immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans As Long
Dim FName As String
Msg = "Would you like to make a backup of this file?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
FName = "F:BACKUP" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs FName
End If
End Sub
This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the SaveCopyAs method to save a backup copy of the file on drive F. If you adapt this procedure for your own use, you need to change the drive and path.
Excel programmers often use a Workbook_BeforeClose procedure to clean up after themselves. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (hiding the status bar, for example), it’s only appropriate that you return the settings to their original state when you close the workbook. You can perform this electronic housekeeping with a Workbook_BeforeClose procedure.
The BeforeSave event, as its name implies, is triggered before a workbook is saved. This event occurs when you choose the File ⇒ Save or File ⇒ Save As command.
The following procedure, which is placed in the Code window for a ThisWorkbook object, demonstrates the BeforeSave event. The routine updates the value in a cell (cell A1 on Sheet1) every time the workbook is saved. In other words, cell A1 serves as a counter to keep track of the number of times the file was saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Dim Counter As Range
Set Counter = Sheets("Sheet1").Range("A1")
Counter.Value = Counter.Value + 1
End Sub
Notice that the Workbook_BeforeSave procedure has two arguments: SaveAsUI and Cancel. To demonstrate how these arguments work, examine the following macro, which is executed before the workbook is saved. This procedure attempts to prevent the user from saving the workbook with a different name. If the user chooses the File ⇒ Save As command, the SaveAsUI argument is True.
When the code executes, it checks the SaveAsUI value. If this variable is True, the procedure displays a message and sets Cancel to True, which cancels the Save operation.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "You cannot save a copy of this workbook!"
Cancel = True
End If
End Sub
Note that this procedure won't really prevent anyone from saving a copy with a different name. If someone really wants to do it, he or she can just open the workbook with macros disabled. When macros are disabled, event-handler procedures are also disabled, which makes sense because they are, after all, macros.
Another category of events consists of activating and deactivating objects — specifically, sheets and workbooks.
Excel can detect when a particular sheet is activated or deactivated and execute a macro when either of these events occurs. These event-handler procedures go in the Code window for the Sheet object.
The following example shows a simple procedure that is executed whenever a particular sheet is activated. This code simply pops up an annoying message box that displays the name of the active sheet:
Private Sub Worksheet_Activate()
MsgBox "You just activated " & ActiveSheet.Name
End Sub
Here’s another example that activates cell A1 whenever the sheet is activated:
Private Sub Worksheet_Activate()
Range("A1").Activate
End Sub
Although the code in these two procedures is about as simple as it gets, event-handler procedures can be as complex as you like.
The following procedure (which is stored in the Code window for the Sheet1 object) uses the Deactivate event to prevent a user from activating any other sheet in the workbook. If Sheet1 is deactivated (that is, another sheet is activated), the user gets a message and Sheet1 is activated.
Private Sub Worksheet_Deactivate()
MsgBox "You must stay on Sheet1"
Sheets("Sheet1").Activate
End Sub
By the way, I don't recommend using procedures, such as this one, that attempt to take over Excel. These so-called "dictator" applications can be very frustrating and confusing for the user. And of course, they can be defeated easily by disabling macros. Rather, I recommend training the user how to use your application correctly.
The previous examples use events associated with a specific worksheet. The ThisWorkbook object also handles events that deal with sheet activation and deactivation. The following procedure, which is stored in the Code window for the ThisWorkbook object, is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub
The Workbook_SheetActivate procedure uses the Sh argument. Sh is a variable that represents the active Sheet object. The message box displays the Sheet object’s Name property.
The next example is contained in a ThisWorkbook Code window. It consists of two event-handler procedures:
Dim OldSheet As Object
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then Set OldSheet = Sh
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Msg As String
If TypeName(Sh) = "Chart" Then
Msg = "This chart contains "
Msg = Msg & ActiveChart.SeriesCollection(1).Points.Count
Msg = Msg & " data points." & vbNewLine
Msg = Msg & "Click OK to return to " & OldSheet.Name
MsgBox Msg
OldSheet.Activate
End If
End Sub
Excel also recognizes the event that occurs when you activate or deactivate a particular workbook. The following code, which is contained in the Code window for the ThisWorkbook object, is executed whenever the workbook is activated. The procedure simply maximizes the workbook’s window.
Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized
End Sub
An example of Workbook_Deactivate code appears next. This procedure is executed when a workbook is deactivated. This procedure copies the selected range whenever the workbook is deactivated. It might be useful if you’re copying data from lots of areas and pasting them into a different workbook. When this event-procedure is in place, you can select the range to be copied, activate the other workbook, select the destination, and press Ctrl+V (or Enter) to paste the copied data.
Private Sub Workbook_Deactivate()
ThisWorkbook.Windows(1).RangeSelection.Copy
End Sub
Simple as it is, this procedure required some experimentation before I got it to work correctly. First, I tried this:
Selection.Copy
This statement didn't work as I intended. It copied the range from the second workbook (the one I activated after deactivating the first workbook). That's because the second workbook became the active workbook after the deactivation event occurred.
This statement didn't work, either. In fact, it gave me a runtime error.
ThisWorkbook.ActiveSheet.Selection.Copy
I eventually remembered the RangeSelection property of a Window object, and that one did the trick. I call this the trial-and-error approach to programming.
In the preceding section, I present examples for 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.
You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example (which is stored in the Code window for a Sheet object), double-clicking a cell in that sheet makes the cell bold (if it’s not bold) or not bold (if it is bold):
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
Target.Font.Bold = Not Target.Font.Bold
Cancel = True
End Sub
The Worksheet_BeforeDoubleClick procedure has two arguments: Target and Cancel. Target represents the cell (a Range object) that was double-clicked. If Cancel is set to True, the default double-click action doesn’t occur.
The default action for double-clicking a cell is to put Excel in cell edit mode. I didn't want that to happen, so I set Cancel to True.
The BeforeRightClick event is similar to the BeforeDoubleClick event except that it consists of right-clicking a cell. The following procedure checks to see whether the cell that was right-clicked contains a numeric value. If so, the code displays the Format Number dialog box and sets the Cancel argument to True (avoiding the normal shortcut-menu display). If the cell does not contain a numeric value, nothing special happens; the shortcut menu is displayed as usual.
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
If IsNumeric(Target) And Not IsEmpty(Target) Then
Application.CommandBars.ExecuteMso ("NumberFormatsDialog")
Cancel = True
End If
End Sub
The Change event occurs whenever any cell in the worksheet is changed. In the following example, the Worksheet_Change procedure effectively prevents a user from entering a nonnumeric value in cell A1. This code is stored in the Code window for a Sheet object.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Not IsNumeric(Target) Then
MsgBox "Enter a number in cell A1."
Range("A1").ClearContents
Range("A1").Activate
End If
End If
End Sub
The single argument for the Worksheet_Change procedure (Target) represents the range that was changed. The first statement checks whether the cell’s address is $A$1. If so, the code uses the IsNumeric function to determine whether the cell contains a numeric value. If not, a message appears, and the cell’s value is erased. Cell A1 is then activated — useful if the cell pointer moved to a different cell after the entry was made. If the change occurs in any cell except A1, nothing happens.
You may be familiar with the Data ⇒ Data Tools ⇒ Data Validation command. This handy feature makes it easy to ensure that only data of the proper type is entered into a particular cell or range. Although the Data ⇒ Data Tools ⇒ Data Validation command is useful, it’s definitely not foolproof.
Try adding data validation to a cell. For example, you can set it up so the cell accepts only a numerical value. It works fine — until you copy another cell and paste it to the data validation cell. Pasting removes the data validation. It's as if it was never there. The severity of this flaw depends on your application. In the next section, I describe how to use the Change event to provide for better validating.
The procedure in this section demonstrates how to prevent users from copying data and wiping out data validation rules. This example assumes that the worksheet has a range named InputArea, and this input area contains data validation rules (set up by choosing the Data ⇒ Data Tools ⇒ Data Validation command). The range can have any validation rules you want.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VT As Long
'Do all cells in the validation range
'still have validation?
On Error Resume Next
VT = Range("InputRange").Validation.Type
If Err.Number <> 0 Then
Application.Undo
MsgBox "Your last operation was canceled. " & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
The procedure is executed whenever a cell is changed. It checks the validation type of the range (named InputRange) that is supposed to contain the data validation rules. If the VT variable contains an error, one or more cells in the InputRange no longer have data validation. (The user probably copied some data over it.) If that's the case, the code executes the Undo method of the Application object and reverses the user’s action. Then it displays a message box, as shown in Figure 11-6. See Chapter 12 for more information about using On Error Resume Next.
The net effect? It’s impossible to wipe out the validation rules by copying data. When Excel is broken, sometimes you can use VBA to fix it.
The events that I discuss previously in this chapter are associated with either a workbook object or a worksheet object. In this section, I discuss two types of events that are not associated with objects: time and keypresses.
The OnTime event occurs when a particular time of day occurs. The following example demonstrates how to get Excel to execute a procedure when the 3 p.m. event occurs. In this case, a robot voice tells you to wake up, accompanied by a message box:
Sub SetAlarm()
Application.OnTime 0.625, "DisplayAlarm"
End Sub
Sub DisplayAlarm()
Application.Speech.Speak ("Hey, wake up")
MsgBox " It’s time for your afternoon break!"
End Sub
In this example, I use the OnTime method of the Application object. This method takes two arguments: the time (0.625 or 3:00 p.m.) and the name of the Sub procedure to execute when the time event occurs (DisplayAlarm).
This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set an OnTime event to remind yourself.
Application.OnTime TimeValue("3:00:00 pm"), "DisplayAlarm"
If you want to schedule an event relative to the current time (for example, 20 minutes from now), you can use a statement like this:
Application.OnTime Now + TimeValue("00:20:00"), "DisplayAlarm"
You can also use the OnTime method to run a VBA procedure on a particular day. You must make sure that your computer keeps running and that the workbook with the procedure is kept open. The following statement runs the DisplayAlarm procedure at 5 p.m. on December 31, 2016:
Application.OnTime DateValue("12/31/2016 5:00 pm"), "DisplayAlarm"
This particular code line could come in handy to warn you that you need to go home and get ready for the New Year’s Eve festivities.
Here’s another example that uses the OnTime event. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event reruns the UpdateClock procedure. The net effect is that cell A1 is updated with the current time every five seconds. To stop the events, execute the StopClock procedure (which cancels the event). Note that NextTick is a module-level variable that stores the time for the next event.
Dim NextTick As Date
Sub UpdateClock()
' Updates cell A1 with the current time
ThisWorkbook.Sheets(1).Range("A1") = Time
' Set up the next event five seconds from now
NextTick = Now + TimeValue("00:00:05")
Application.OnTime NextTick, "UpdateClock"
End Sub
Sub StopClock()
' Cancels the OnTime event (stops the clock)
On Error Resume Next
Application.OnTime NextTick, "UpdateClock", , False
End Sub
Call StopClock
The OnTime method has two additional arguments. If you plan to use this method, you should refer to the Help system for complete details.
While you work, Excel constantly monitors what you type. Because of this, you can set things up so a keystroke or a key combination executes a procedure.
Here’s an example that reassigns the PgDn and PgUp keys:
Sub Setup_OnKey()
Application.OnKey "{PgDn}", "PgDn_Sub"
Application.OnKey "{PgUp}", "PgUp_Sub"
End Sub
Sub PgDn_Sub()
On Error Resume Next
ActiveCell.Offset(1, 0).Activate
End Sub
Sub PgUp_Sub()
On Error Resume Next
ActiveCell.Offset(-1, 0).Activate
End Sub
After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row.
Notice that the key codes are enclosed in braces, not in parentheses. For a complete list of keyboard codes, consult the Help system. Search for OnKey.
In this example, I use On Error Resume Next to ignore any errors that are generated. For example, if the active cell is in the first row, trying to move up one row causes an error that can safely be ignored. And if a chart sheet is active, there is no active cell.
By executing the following routine, you cancel the OnKey events:
Sub Cancel_OnKey()
Application.OnKey "{PgDn}"
Application.OnKey "{PgUp}"
End Sub
Application.OnKey "%{F4}", ""
Although you can use the OnKey method to assign a shortcut key for executing a macro, you should use the Macro Options dialog box for this task. For more details, see Chapter 5.