Using ON.KEY() in Excel

ID: Q58951

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

ON.KEY() must be used in the following format:


   ON.KEY("<key_text>","<macro_sheet>!<Name>")

For example:


   ON.KEY("%1","MACRO1.XLM!Restart")

The second argument must be an external reference to a defined name, not a cell reference. In the Windows versions of Microsoft Excel, the second argument must also be in quotation marks (the OS/2 version does not require the quotation marks).

Using a cell reference instead of a name (for example, "MYMACROS.XLM!$B$32" instead of "MYMACROS.XLM!Return") or omitting the quotation marks in Windows versions will cause an error to be generated when the key sequence represented by "key text" is pressed. An error will also be generated if the filename contains any special characters, such as dollar signs.

MORE INFORMATION

A working example is as follows:


   A1: AutoSaver
   A2: =IF(ISERROR(Count>1),SET.NAME("Count",0))
   A3: =ON.KEY("%1","MACRO1.XLM!Restart")
   A4: =RETURN()
   A5: Restart
   A6: =SAVE.AS("WORK"&SaveCount&".XLS")
   A7: =SET.NAME("SaveCount",SaveCount+1)
   A8: =RETURN()

This macro, once executed, defines ALT+1 to automatically save the active document with the name WORK and a count number (for example, WORK27.XLS). Each time ALT+1 is pressed, the number increments itself, so a running set of work files is generated. This macro assumes that Restart has been defined on the macro sheet as a command macro at $A$5. If this macro is to be used over multiple work sessions, then the macro sheet should be saved at the end of each work session, which will save SaveCount so it won't need to be reset. Each time a new work session is begun, the macro must be reexecuted, or it should be defined as an AutoOpen macro.

One interesting use of ON.KEY() in conjunction with SEND.KEYS() allows a macro to send keystrokes to Microsoft Excel itself, just as if the user had typed in this information. For example:


   .
   .
   .
   A14: =ON.KEY("{F13}","MACRO1.XLM!Continue")
   A15: =SEND.KEYS("{F2}^{RIGHT}{F9}~{F13}")
   A16: =RETURN()
   A17: Continue
   A18: =ON.KEY("{F13}")
   .
   .
   .

Cell A14 sets F13 [a useful designator for this purpose, since many keyboards don't have F13 keys, although Microsoft Excel will still recognize it when sent with SEND.KEYS()] to run the Continue macro on sheet MACRO1.XLM. For this to work properly, Continue should be defined as a command macro at cell A17. Cell A15 sends the following key sequence to the keyboard buffer:


   "F2 CTRL+RIGHT ARROW F9 ENTER F13"

Cell A16 "ends" the macro, returning control to the user.

As soon as control returns to the user, Microsoft Excel checks the keyboard buffer. Since a key sequence is already present, Microsoft Excel begins interpreting the keys. In this example, F2 activates the formula bar. CTRL+RIGHT ARROW selects the entire formula in the formula bar. F9 converts the selected formula to a value. ENTER stores the value in the current cell. Then, F13 causes the Continue macro to begin execution, which is the continuation of our macro (cell A17) -- all as if the user had entered these keys from the keyboard.

The command in cell A18 is important. Without a second argument, the ON.KEY command "undefines" the key indicated, restoring it to its previous function. If this is not done, any time the user presses F13 (and a macro isn't running), the macro Continue will begin execution -- a jump to A17.

Note: the information above also applies to version 5.0 only when using version 4.0 macro language.

REFERENCES

For more information on specifying "key text":

"Microsoft Excel Functions and Macros," version 2.0 for Windows, pages 373-375 (Appendix)

"Microsoft Excel Functions and Macros," version 2.2 for OS/2, pages 323-327 (ON.KEY)

Note: When using ALT+<key>, CTRL+<key>, and SHIFT+<key> combinations (for example, CTRL+3), do not use parenthesis to indicate the primary key.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 4.0a 4.00a 5.0


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 16, 1999