Excel VBA copy cells on key press -

i'm using vba dynamically change worksheet while typing cell. so, i've been using api code found here:

excel track keypresses

so sub sheet_keypress describes desired action upon pressing key. however, i've been running problems following:

private sub sheet_keypress(byval keyascii integer, _                         byval keycode integer, _                        byval target range, _                        cancel boolean)     dim col string     col = chr(keyascii)     worksheets(1).range("g" & 4 & ":g" & 6).value = _         worksheets(1).range(col & 1 & ":" & col & 3).value end sub 

when go sheet , type somewhere not in rows 1-3, first keypress fine. however, second keypress not recorded, , further key gives error 1004. causing error , possible avoid it?

the problem appears stem fact excel locks worksheets being changed whilst in "edit mode" (i.e. editing value in cell) until after exit edit mode pressing enter or escape.

so in scenario, looks when first press key registered fine , triggers sheet_keypress, however, after this, excel goes "edit mode" , subsequent call worksheets(1).range("g" & 4 & ":g" & 6).value fails excel locks sheet. nevertheless, excel still registers key press, , can see if, example change last row of routine application.statusbar = col.

as ways around this, need write code in such way kicked excel out of "edit mode" after key press. unfortunately, there no built-in way this, people have come various ways (e.g. here)

there simpler solution, however, there couple of caveats: value typed in not display in excel sheet, , able type single value before formula updated (so no > column aa).

what can put line cancel = true sub above, , prevent key press going excel , activating edit mode.