SendKeys in VBA

The SendKeys statement in VBA is one of the more interesting features because you can use it to send keystrokes directly to the user interfacejust as if you were entering the keystrokes yourself. This means you can use SendKeys to perform all sorts of tasks that you might not ordinarily be able to perform using other statements. For example, you could use SendKeys to modify the Ribbon interface programmatically.

Unfortunately, SendKeys can also cause problems for the VBA developer. I’ve received more than a few e-mails regarding SendKeys examples not working properly. For example, Listing 5-8 in VBA for Dummies causes a lot of woe for many readers. The natural inclination is to want to watch the macro run from within the Visual Basic for Applications editor, but you can’t do this with SendKeys. The SendKeys statement always sends its output to the active windowno matter which window that might be. So, if you have the Visual Basic for Applications editor selected, the SendKeys output goes to that window, rather than the Excel window where the output would normally go when a user is running the macro.

So, just how do you run this macro? If you want to see the macro work in the debugger, you begin by setting a breakpoint in the editor. Simply click in the margin area on the left side of the editor and you’ll see a breakpoint added like the one shown here:

SendKeys01

The breakpoint line appears highlighted in red. You also see a red circle in the left margin. When you run the macro, it’ll stop at the ActiveRows = ActiveWindow.RangeSelection.Rows.Count line.

At this point, you can run the macro. Open the Developer tab and you’ll see a Macros button in the Code group as shown here:

SendKeys02

Click the Macros button and you’ll see the Macro dialog box shown here:

SendKeys03

Select the ChangeAllRooms macro from the list. Click Run. The macro will run to the breakpoint you selected.

Now, if you run the macro shown in Listing 5-8, you’ll see that SendKeys is actually broken in VBA in Office 2010 (it worked fine in earlier versions of Office). The key combination is sent, but usually after the rest of the macro has run. The second part of the callthe part that tells VBA to wait until the SendKeys call is processed before proceeding is broken. Consequently, the code to tell Excel to select a column by pressing the Ctrl+Shift+Down Arrow keys doesn’t work. The key combination is sent to the window, but long after the need for the key combination has passed. If there is someone who has a solution to this problem, let me know at John@JohnMuellerBooks.com. In the meantime, you can get this example to work using the following code instead.

Public Sub ChangeAllRooms()
    Dim ActiveRows As Integer   ' Number of active rows.
    Dim Counter As Integer      ' Current row in process.
     
    ' Select the first data cell in the worksheet.
    Range("A5").Select
     
    ' Use SendKeys to select all of the cells in the column.
    ' This call is broken in Office 2010, so I commented it out.
    'SendKeys "^+{DOWN}", True
    ' Select the range manually for the example instead.
    Range("A5", "A10").Select
     
    ' Get the number of rows to process.
    ActiveRows = ActiveWindow.RangeSelection.Rows.Count
     
    ' Reset the cell pointer.
    Range("C5").Select
     
    ' Keep processing the cells until complete.
    For Counter = 5 To ActiveRows + 5
     
        ' Call the Sub created to change a single cell.
        MakeChoice3
         
        ' Move to the next cell.
        Range("C" + CStr(Counter)).Select
    Next
End Sub

OK, now that you have working code, you can run the example. Here are the results you should see from Listing 5-8.

SendKeys04