KeyUp execute code.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,026
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,613
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
2,026
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
8,613
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
2,026
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
2,026
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
2,026
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
8,613
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
2,026
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
2,026
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top