XL: Error Using SendKeys in VB with Shortcut Key Assigned

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you use Visual Basic for applications to enter text into the active cell, and you use the SendKeys command to set the active cell to edit mode, the macro will fail when it is run from the shortcut key assigned to the macro.

Note, however, that the macro works correctly if you run it from a button or by clicking Macro on the Tools menu.

CAUSE

This problem occurs when you invoke the macro by using the shortcut key CTRL+<key> (where <key> is the key assigned to the macro>). The SendKeys statement is run immediately in this macro. Because it is extremely unlikely that you will be able to release the CTRL key before the SendKeys is executed, a key combination of CTRL+F2 is sent to Microsoft Excel, which brings up the information window.

WORKAROUND

To avoid this problem, add a wait period of one second before the SendKeys command is executed. This allows the macro to execute properly.

Microsoft provides examples of Visual Basic for applications 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.

Sample Macro

   Sub Test()
      ActiveCell.Formula = "ANY TEXT"
      Application.Wait (Now() + TimeValue("00:00:01"))
      SendKeys "{f2}", False
   End Sub

MORE INFORMATION

The Wait method pauses a macro that is running until a specified time is reached. The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer. Background processes, such as printing and recalculation, are continued.