Using the OnKey Method in a Visual Basic Macro

ID: Q133136

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

You can use the OnKey method in conjunction with SendKeys to allow a Visual Basic, Applications Edition, macro to send keystrokes to Microsoft Excel as if the user had entered information.

The "More Information" section of this article contains a sample procedure demonstrating this method.

MORE INFORMATION

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Sub TestIt()

   ' This line sets up the F13 key to run the Continue macro. This is a
   ' useful designator for this purpose: many keyboards don't have F13
   ' keys, but Microsoft Excel will still recognize it when it's sent with
   ' SendKeys.

   Application.OnKey "{F13}", "Continue"

   ' This line sends the F2 key to activate the Formula bar. SHIFT+END
   ' selects the entire formula, and the F9 key converts the formula to a
   ' value. The ENTER key enters the value in the active cell, and the F13
   ' key runs the Continue macro.

   Application.SendKeys ("{F2}+{End}{F9}~{F13}")

End Sub

Sub Continue()

   'This line sets the F13 key back to a Null value.
   Application.OnKey "{F13}"

End Sub

The Continue macro is important. Without a second argument, the OnKey method 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.

REFERENCES

For information about using the ONKEY macro function in earlier versions of Microsoft Excel, query on the following article in the Microsoft Knowledge Base:

   ARTICLE ID:  Q58951
   TITLE:  ON.KEY() in Excel

Additional query words: 7.00

Keywords          : kbprg xlwin 
Version           : 5.00 5.00c 7.00
Platform          : WINDOWS

Last Reviewed: September 4, 1997