Excel VBA copy cells on key press

Question

I'm using VBA to dynamically change the worksheet while typing into another cell. To do so, I've been using the API code that is found here:

Excel track keypresses

So the Sub Sheet_Keypress describes the desired action upon pressing a key. However, I've been running into problems with the following:

Private Sub Sheet_KeyPress(ByVal KeyAscii As Integer, _ 
                       ByVal KeyCode As Integer, _
                       ByVal Target As Range, _
                       Cancel As Boolean)
    Dim Col As String
    Col = Chr(KeyAscii)
    Worksheets(1).Range("G" & 4 & ":G" & 6).Value = _
        Worksheets(1).Range(Col & 1 & ":" & Col & 3).Value
End Sub

When I go back to the sheet and type somewhere not in rows 1-3, the first keypress does fine. However, the second keypress is not recorded, and a further key gives Error 1004. What exactly is causing this error and is it possible to avoid it?

Hello (As far as i know) There are no KeyPress or KeyASCII events for Excel Worksheet — funk, Aug 20 '13 at 06:35
First make sure the keystroke corresponds to a valid column and then include a DoEvents at the end of the sub. — Gary's Student, Aug 20 '13 at 11:37

score 0 · Answer 1 · answered Aug 20 '13 at 13:57

The problem appears to stem from the fact that Excel locks the worksheets from being changed whilst you are in "edit mode" (i.e. editing a value in a cell) until after you exit edit mode by pressing Enter or Escape.

So in your scenario, it looks like that when you first press a key it is registered fine and triggers your Sheet_Keypress, however, immediately after this, Excel goes into "edit mode" and then your subsequent call to Worksheets(1).Range("G" & 4 & ":G" & 6).Value fails as Excel locks the sheet. Nevertheless, Excel still registers the key press, and you can see this if, for example you change the last row of your routine to something like Application.StatusBar = Col.

As to ways around this, you would need to write code in such a way that it kicked Excel out of "edit mode" just after the key press. Unfortunately, there is no built-in way to do this, people have come up with various ways to do this (e.g. here)

There is a simpler solution, however, there are a couple of caveats: the value typed in will not display in the Excel sheet, and you will only be able to type a single value before your formula is updated (so no good for > column AA).

What you can do is to put the line Cancel = True into your sub above, and this will prevent the key press from going into Excel and thus activating the edit mode.

Excel VBA copy cells on key press

1 Answers1