How to intercept each pressed key

[Stefan Mueller]

In real VB I can intercept each pressed key with the following code
before e.g. the character of the pressed key is added to a TextBox:

Private Sub Form_Load()
Me.KeyPreview = True
...
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
MsgBox ("The key " & KeyCode & " has been pressed.")
End Sub

Is there a way to do something similar in VBA (e.g. Excel)?

 

[Jim Thomlinson]

Are you wanting to intercept key strokes within a cell? If so then the answer
is no as no events fire while a cell is in edit mode.

 

[Greg Wilson]

I concur with Jim. Further to his response, the only way to do this is to run
a loop and monitor keystrokes using API code. I think PeekMessage and
DispatchMessage. If the need is only temporary and it's particularaly
important, then it may be worth it. But I doubt it.

Say, you click a button and it goes into this loop where it monitors each
keystroke and acts as if Excel is in edit mode but isn't actually. You are
intercepting the keystrokes and concatenating to the active cell and also
doing whatever with each keystroke. I don't think you would want to invoke
the loop by double clicking the same way you would go into Excel's edit mode.

Regards,
Greg

 

[Stefan Mueller]

No, I'd like to intercept key strokes within a UserForm.
E.g. if you press F5 I'd like to do a refresh of a ListBox.