Using the OnKey Method in a Visual Basic Macro

Last reviewed: September 3, 1997
Article 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 PgmHowTo xlwin kbcode
Version : 5.00 5.00c 7.00
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.