Some Event Like Methods

Excel's Application object comes with several methods that are "event-like." In other words, they respond to outside influences such as the press of a key. This section looks at four of these methods: OnKey, OnTime, OnRepeat, and OnUndo.

Running a Procedure when the User Presses a Key

When recording a macro, Excel enables you to assign a Ctr\+key shortcut to a procedure. However, there are two major drawbacks to this method:

■ Excel uses some Ctrl+key combinations internally, so your choices are limited.

■ It doesn't help if you would like your procedures to respond to "meaningful" keys such as Delete and Esc.

To remedy these problems, use the Application object's OnKey method to run a procedure when the user presses a specific key or key combination:

Application.OnKey(Key[, Procedure])


The key or key combination that runs the procedure. For letters, numbers, or punctuation marks, enclose the character in quotes (for example, "a"). For other keys, see Table 8.1.

The name (entered as text) of the procedure to run when the user presses a key. If you enter the null string ("") for Procedure, a key is disabled. If you omit Procedure, Excel resets the key to its normal state.

Excel's Application Object 143


What to Use

Right arrow


Scroll Lock




Up arrow


F1 through F12

"{F1}" through "{F15}"

You also can combine these keys with the Shift, Ctrl, and Alt keys. You just precede these codes with one or more of the codes listed in Table 8.2.

Table 8.2

Symbols That Represent Alt, Ctrl, and Shift in OnKey


What to Use


% (percent)


~ (caret)


+ (plus)

For example, pressing Delete normally wipes out only a cell's contents. If you would like a quick way of deleting everything in a cell (contents, formats, comments, and so on), you could set up (for example) Ctrl+Delete to do the job. Listing 8.2 shows three procedures that accomplish this:

■ SetKey—This procedure sets up the Ctrl+Delete key combination to run the DeleteAll procedure. Notice how the Procedure argument includes the name of the workbook where the DeleteAll procedure is located; therefore, this key combination will operate in any workbook.

■ DeleteAll—This procedure runs the Clear method on the currently selected cells.

■ ResetKey—This procedure resets Ctrl+Delete to its default behavior.

0 0

Post a comment