KeyUp execute code.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
Short of using a keyboard hook, you could use a loop as follows :

In a Standard Module:
Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows
Short of using a keyboard hook, you could use a loop as follows :

In a Standard Module:
Code:
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
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)
Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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)
Code:
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.
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
7,977
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,893
Office Version
  1. 2019
Platform
  1. Windows
[COLOR=#333333 said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,903
Messages
5,544,974
Members
410,647
Latest member
LegenDSlayeR
Top