KeyUp execute code.

[omairhe]

Hey excel gurus,

I would need your help on running a specific code when key state is up. I am already aware of a OnKey method but that is not what I require.

Another way of achieving this would be to use AutoHotKey. With a hotkey assigned to the excel macro that gets triggered on key up state that works only on excel.

will appreciate.
Thank you.

 

[Jaafar Tribak]

Short of using a keyboard hook, you could use a loop as follows :

In a Standard Module:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Const HOTKEY = "a"  [B][COLOR=#008000]'<== Change hotkey as required.
[/COLOR][/B]

Sub HookKey()
    Application.OnKey HOTKEY, "Macro"
End Sub

Sub UnHookKey()
    Application.OnKey HOTKEY
End Sub

Function HiWord(ByVal dw As Long) As Integer
    If dw And &H80000000 Then
        HiWord = (dw \ 65535) - 1
    Else
        HiWord = dw \ 65535
    End If
End Function

Sub Macro()

    [B][COLOR=#008000]'Wait until the hotkey is up.[/COLOR][/B]
    Do While HiWord(GetKeyState(Asc(UCase(HOTKEY))))
        DoEvents
    Loop

    [B][COLOR=#008000]'user released the hotkey.[/COLOR][/B]
    MsgBox "key :  '" & HOTKEY & "'  is up!", vbInformation
    
End Sub

 

[omairhe]

Short of using a keyboard hook, you could use a loop as follows :

In a Standard Module:

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If


Const HOTKEY = "a"  [B][COLOR=#008000]'<== Change hotkey as required.
[/COLOR][/B]

Sub HookKey()
    Application.OnKey HOTKEY, "Macro"
End Sub

Sub UnHookKey()
    Application.OnKey HOTKEY
End Sub

Function HiWord(ByVal dw As Long) As Integer
    If dw And &H80000000 Then
        HiWord = (dw \ 65535) - 1
    Else
        HiWord = dw \ 65535
    End If
End Function

Sub Macro()

    [B][COLOR=#008000]'Wait until the hotkey is up.[/COLOR][/B]
    Do While HiWord(GetKeyState(Asc(UCase(HOTKEY))))
        DoEvents
    Loop

    [B][COLOR=#008000]'user released the hotkey.[/COLOR][/B]
    MsgBox "key :  '" & HOTKEY & "'  is up!", vbInformation
    
End Sub

I will really appreciate if you can also make this work on 64 bit excel. As I am currently using 64 bit. 2019. So that I can test your code.

Thanks a lot.

 

[Jaafar Tribak]

It should work on 64bit excel

BTW, in order to avoid the distracting/confusing wait cursor that comes up when holding the hotkey down, you could amend the Macro code as follows : (changes in red)

Sub Macro()

    On Error GoTo errHandler
    
[COLOR=#008000]    'Wait until the hotkey is up.[/COLOR]
    Do While HiWord(GetKeyState(Asc(UCase(HOTKEY))))
[COLOR=#ff0000]        Application.Cursor = xlNorthwestArrow[/COLOR]
        DoEvents
    Loop

   [COLOR=#008000]'User released the hotkey.[/COLOR] 
[COLOR=#ff0000]    Application.Cursor = xlDefault[/COLOR]
    MsgBox "key :  '" & HOTKEY & "'  is up!", vbInformation
    
errHandler:
[COLOR=#ff0000]    Application.Cursor = xlDefault[/COLOR]

End Sub

 

[omairhe]

It should work on 64bit excel

BTW, in order to avoid the distracting/confusing wait cursor that comes up when holding the hotkey down, you could amend the Macro code as follows : (changes in red)

Sub Macro()

    On Error GoTo errHandler
    
[COLOR=#008000]    'Wait until the hotkey is up.[/COLOR]
    Do While HiWord(GetKeyState(Asc(UCase(HOTKEY))))
[COLOR=#ff0000]        Application.Cursor = xlNorthwestArrow[/COLOR]
        DoEvents
    Loop

   [COLOR=#008000]'User released the hotkey.[/COLOR] 
[COLOR=#ff0000]    Application.Cursor = xlDefault[/COLOR]
    MsgBox "key :  '" & HOTKEY & "'  is up!", vbInformation
    
errHandler:
[COLOR=#ff0000]    Application.Cursor = xlDefault[/COLOR]

End Sub

If I press the character "a" from the keyboard, it will not do anything. I put the code in a standard module.
Could you please attach the working workbook, so that I can confirm that I am not doing something wrong?

and second instead of "a" what changes in the code so that all four arrow keys will run macro on key up state. meaning up, down, right and left arrow keys..

Thank you.

 

[omairhe]

Dear Jaafar,

the code is now working with letter a. As I did not changed the HOTKEY with "a" previously.

Could you make it work for arrow keys too?

thank you.

 

[omairhe]

Oh no. there is one more thing. The code is not truely a Key up state.
when I keep the letter "a" pressed down the pop up displays immidiately, but my requirement was to do that when the letter "a" is released/up not when it is pressed/down.

 

[Jaafar Tribak]

The code should fire only when the hotkey is released/up ...

I re-tested the code and it works as expected ie: when the 'a' key is released.

What version of excel and Windows are you using ?

 

[omairhe]

The code should fire only when the hotkey is released/up ...

I re-tested the code and it works as expected ie: when the 'a' key is released.

What version of excel and Windows are you using ?

I too retested the code and to my surprise the "a" is now working as expected, meaning on up key state.

However when I try to do the same with the arrow key it will omit it and show my msg box as soon as I press the down arrow key.

Will appreciate further assistance on this.

 

[omairhe]

What version of excel and Windows are you using ?[/COLOR].

I am using windows 10 pro 64 and microsoft office 2019 64 bit

P.S. "a" is working. the arrow keys are not.