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.

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.

editcomment01

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.

macrorun02

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!

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

_________________________

You may also like...

2 Responses

  1. Taylor says:

    Thank you! Very quick solution and explained clearly. Everything else I found said you shouldn’t use sendkeys. That may be true, but didn’t solve my problem. :)

  2. Allan Brayshaw says:

    I don’t think the Sendkeys Wait is working in latest excel 2010 – have you experienced any problem? I have test code if you are interested.

Leave a Reply to Allan Brayshaw Cancel reply

Your email address will not be published. Required fields are marked *