How to get Last Key or Key-Combo Pressed (Not Event)

[johnywhy]

Hi

i'd like to get the last key or key-combo pressed.
Windows API method is fine, or VBA.

Note: I need to get this several seconds AFTER they key is pressed. I do not care about the current state of the key. I'm not trying to run a macro in response to a keypress. So, getasynckeystate and OnKey are NOT the appropriate solutions for this. The key might be pressed while a macro is running, and i want to allow the macro to finish running without interruption, and only get the last key pressed after the key has already been released, possibly several seconds before.

I'm not trying to detect any specific key, just want to get last keypress, whatever key it was.

The method should be able to detect special keys, like home, end, left, right, down, and up.

It should also recognize key combinations, like control alt and shift.

Using Excel 2007.

Thanks!

also posted here:
excelforum.com/excel-programming-vba-macros/850033-how-to-get-last-key-or-key-combo-pressed-not-event.html

[johnywhy]

hi

not sure how to mark this solved, but I got it. The neat thing about this solution is that if you put a loop around it, you can get all keystrokes in the buffer, not just the most recent. Even better, you can get mousecklicks.

The answer is the API function, PeekMessage.

Here's the declaration:

Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (ByRef lpMsg As MSG, ByVal hWnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long

Example call:

lResult = PeekMessage(msgMessage, hWnd, WM_KEYDOWN, WM_KEYDOWN, PM_REMOVE + PM_NOYIELD)

You also need the TranslateMessage and FindWindow fuctions, constants, message data-structures, and a few other bits.

Cheers!

[johnywhy]

A full example:
mrexcel.com/forum/showthread.php?254206-Flushing-the-Keyboard-Buffer&p=1264669&viewfull=1#post1264669

Doc:
msdn.microsoft.com/en-us/library/windows/desktop/ms644943%28v=vs.85%29.aspx

[Paul_Hossler]

Up in thread tools above your first post, there's a Solved button

Interesting

Thanks


Paul

[Kenneth Hobs]

hwnd = Application.Hwnd

[Paul_Hossler]

Ken -- Thanks,

Paul

[johnywhy]

Note, this only gets key and mouse events that were waiting to be processed while your macro was running. Key/mouse events that were processed normally before your macro blocked input will not be returned by this method, as they are long gone.

You actually do not need the FindWindow API function, since VBA provides a built-in way to get the Excel window handle, provided by Ken above. Thanks Ken!

[Paul_Hossler]

How can you tell if it's a Shifted-q?

Now if I run the loop, the pressing the shift key get's 'peeked'

Option Explicit
' Type to hold the coordinates of the mouse pointer

Private Type POINTAPI
    x As Long
    y As Long
End Type

' Type to hold the Windows message information

Private Type MSG
    hWnd As Long
    ' the window handle of the app message As Long
    ' the type of message (e.g. keydown)
    wParam As Long
    ' the key code lParam As Long
    ' not used time As Long
    ' time when message posted
    pt As POINTAPI
    ' coordinate of mouse pointer
End Type
  
'Look in the message buffer for a message

Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (ByRef lpMsg As MSG, ByVal hWnd As Long, _
    ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
    ' Translate the message from a key code to a ASCII code

Private Declare Function TranslateMessage Lib "user32" (ByRef lpMsg As MSG) As Long
    ' Windows API constants
Private Const WM_CHAR As Long = &H102
Private Const WM_KEYDOWN As Long = &H100
Private Const PM_REMOVE As Long = &H1
Private Const PM_NOYIELD As Long = &H2
' Check for a key press

Public Function CheckKeyboardBuffer() As String
    ' Dimension variables
    Dim msgMessage As MSG
    Dim hWnd As Long
    Dim lResult As Long
    ' Get the window handle of this application
    hWnd = Application.hWnd
    ' See if there are any "Key down" messages
    lResult = PeekMessage(msgMessage, hWnd, WM_KEYDOWN, WM_KEYDOWN, PM_REMOVE + PM_NOYIELD)
    ' If so ...
    If lResult <> 0 Then
        '... translate the key-down code to a character code,
        ' which gets put back in the message queue as a WM_CHAR message ...
        lResult = TranslateMessage(msgMessage)
        '... and retrieve that WM_CHAR message
        lResult = PeekMessage(msgMessage, hWnd, WM_CHAR, WM_CHAR, PM_REMOVE + PM_NOYIELD)
        ' Return the character of the key pressed, ignoring shift and control characters
        CheckKeyboardBuffer = msgMessage.wParam
    End If
End Function
 
Sub test()
    Dim i As Long
    Dim s As String
    s = ""
    i = 1
    Do While Len(s) = 0
        ActiveSheet.Cells(i, 1).Value = i
        i = i + 1
        s = CheckKeyboardBuffer
    Loop
    MsgBox s
End Sub

Paul