Problems With SendKeys in Excel

Yes, I know that it’s a bad idea to use the SendKeys method in Excel, because strange things can happen.

However, it’s handy in a few situations, and I use SendKeys in a few of my Comments macros.

SendKeys Example

For example, in this macro to insert a blank comment, without a user name, the comment opens for editing, at the end of the macro.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment text:=""
  End If
  SendKeys "+{F2}"
End Sub

Send Keyboard Shortcuts

In that example, the SendKeys line simulates using the keyboard shortcut – Shift + F2 – to edit the comment in the active cell.

SendKeys Doesn’t Run

While I was updating the Comments VBA page, I wanted to test a few of the macros, to make sure that they still worked in Excel 2010. To make it easier to run a macro, I added a keyboard shortcut for it – Ctrl + Shift + C.

Shortcut Problem

When I tested the macro, using that shortcut, it inserted the comment, but the comment didn’t open for editing. Hmmm…maybe that shortcut code doesn’t work in Excel 2010.

But, when I ran the macro from the Macro window, instead of the shortcut, it worked correctly. So, the problem wasn’t the SendKeys code.

There was something funny happening with the shortcut to run the macro.

Add a Wait Line

Some Googling led me to the Microsoft site, where this problem is in the MSKB: Error Using SendKeys in VB with Shortcut Key Assigned

The problem occurs because this is a very short macro, and I was still pressing the Ctrl + Shift keys when the macro runs the SendKeys statement. And that messes up the SendKeys keystrokes. See – I told you that SendKeys was risky!

Suggested Solution

The suggested solution is to add a Wait line in the macro, just before the SendKeys code. So, I altered the macro, and now it runs correctly when I use the keyboard shortcut.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment Text:=""
  End If
    Application.Wait (Now() + TimeValue("00:00:01"))
    SendKeys "+{F2}"
End Sub

_________________________