Keyboard Hook to be used as shortcut to fire up macro outside excel

[mikerek]

Hello guys!

Please tell me if it is possible initialize procedure outside inactive Excel. I mean, the file is opened, and some userform is loaded. Is it somehow possible (e.g. hooking keyboard) to assign to procedure/userfrom_button, a windows shortcut (e.g. alt+1)? The macro is being used for printscreening current window (using keybd_event), but I need it improved to use only shortcut instead of clicking userform button.

Can you please advise me on that?
Thank You!

 

[Coding4Fun]

I am not sure I fully understand the request, are you trying to open an excel file and then run a macro or procedure stored in excel using a keyboard shortcut alt+1 as the example above?

I may missing a complexity in the equation but it sounds like you are pressing a button from within an excel file which you want to be able to use the specified shortcut rather than using the button.

If this is an accurate statement then you should have created a macro and assigned it to this button. Figure out which macro you need to adjust then navigate View Tab -> Macros -> View Macros -> Select the macro in the list -> Options -> you should see an option for adding a shortcut. Note that this is limited to Ctrl + button

 

[Jaafar Tribak]

You can set an acceleration key for the userform button via the VBE Properties window .. so for example, if the caption of the button is CommandButton1 and you assign the letter "C" as the acceleration key then the macro associated with the button is executed whenever you press ALT+C

 

[mikerek]

Thank you guys for response!
I know about acceleration key etc. but the issue here is to run the macro while excel is opened but inactive (minimized). For example I want my macro to start while I am at Google Chrome window (and Excel is still minimized).
Going through, My macro captures screen and pasting ss to Word file. Right now I am using always-on-top user form for that, but it is inconvenient to have it always on the screen, so i thought that it will be nice to initiate macro outside excel using some shortcut. Do you know what I mean?
Thanks!

 

[GTO]

If I understand, as long as another application (Chrome in this case) has the focus, that is where keystrokes are going.

 

[MrIfOnly]

Here's an option you can look into, although I'm not sure how well it will work if Excel is minimized as opposed to closed.

Create a VBScript file to run your macro (instructions for this can be found here: Run Excel Macro Without Opening Excel - wellsr.com)

Then create a shortcut of the vbs file, and lastly, assign a shortcut key in the properties of that shortcut.

If you do try this, please report back on how it worked out for you.

CJ

 

[mikerek]

MrIfOnly, thanks for the idea, but it is not my point, will explain below.
GTO, right, but is there any chance to bypass it?

Sorry guys if I wan not to precise in description of my problem. I am creating macro which should print-screen the last active window and paste it to Word.
I have achieved it this way:

#If Win64 Then
    Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As LongPtr)
#Else
    Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As Long)
#End If


'sleep
#If Win64 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

'sleep
#If Win64 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

'keyboard events
Private Const KEYEVENTF_KEYUP = &H2
Private Const VK_ALT = &H12
Private Const VK_TAB = &H9
Public Const VK_SNAPSHOT = &H2C


Sub SS()


    ' Press Alt.
    keybd_event VK_ALT, 0, 0, 0
    DoEvents
    Sleep 10
    ' Press Tab.
    keybd_event VK_TAB, 1, 0, 0
    DoEvents
    Sleep 10
    ' Release Alt.
    keybd_event VK_ALT, 0, KEYEVENTF_KEYUP, 0
    DoEvents
    
    'Application.Wait waitTime
    Sleep 100
    
    'Press PrtSc + alt
    keybd_event VK_ALT, 0, 0, 0
    keybd_event VK_SNAPSHOT, 1, 0, 0
    keybd_event VK_ALT, 0, KEYEVENTF_KEYUP, 0
    
End Sub

For convenience, the code is assigned to always-on-top userform, but it is viable on taken screenshots :|
Nevertheless, main question is how to invoke the procedure when the focus is on another application? And if it is possible?
For now I have to click on active window (e.g. Chrome) then on userform to change the focus on it, and then on userform button.

Or maybe you guys have another idea to solve this problem?

 

[GTO]

GTO, right, but is there any chance to bypass it?

If it is possible, I believe you would have to create a low-level hook that dependably catches and correctly processes the key-combo of interests, without interfering with any other low-level hooks in place. I am way late but found one article Here.

Mark

 

[Jaafar Tribak]

Alternatively, you can use the RegisterHotKey API which is easier to code , less costly on the system and less prone to dangerous crashes than using a system wide low level hook .

Here is an example that shows how to invoke a Macro associated with a userfom by pressing ALT+ 1 regardless of whether the focus is currently on excel, the userform or on another application :

To test this; add a new userform to the project and place this code in its module :

Private Type POINTAPI
    x As Long
    y As Long
End Type

#If VBA7 Then
    Private Type MSG
        hwnd As LongPtr
        message As Long
        wParam As LongPtr
        lParam As LongPtr
        time As Long
        pt As POINTAPI
    End Type

    Private Declare PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long) As Long
    Private Declare PtrSafe Function PeekMessage Lib "user32" Alias "PeekMessageA" (lpMsg As MSG, ByVal hwnd As LongPtr, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
    Private Declare PtrSafe Function WaitMessage Lib "user32" () As Long
#Else
    Private Type MSG
        hwnd As Long
        message As Long
        wParam As Long
        lParam As Long
        time As Long
        pt As POINTAPI
    End Type
    
    Private Declare Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
    Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" (lpMsg As MSG, ByVal hwnd As Long, ByVal wMsgFilterMin As Long, ByVal wMsgFilterMax As Long, ByVal wRemoveMsg As Long) As Long
    Private Declare Function WaitMessage Lib "user32" () As Long
#End If

Private Const MOD_ALT = &H1
Private Const NUMPAD1 = &H61
Private Const PM_REMOVE = &H1
Private Const WM_HOTKEY = &H312

Private bCancel As Boolean

Private Sub UserForm_Activate()
    
   [COLOR=#008000] '============================================================
    'run any other pre-existing code here before hooking the keys.
    '============================================================[/COLOR]
    
    [COLOR=#008000]'Hook the Alt + 1 keys combination.[/COLOR]
    bCancel = False
    Call RegisterHotKey(Application.hwnd, &HBFFF&, MOD_ALT, vbKey1)
    Call RegisterHotKey(Application.hwnd, &HBFFF&, MOD_ALT, NUMPAD1)

    Call Key_Listener
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bCancel = True
End Sub

Private Sub Key_Listener()
    Dim message As MSG
    
    On Error GoTo Oops
    
    Do While Not bCancel
        WaitMessage
            If PeekMessage(message, Application.hwnd, WM_HOTKEY, WM_HOTKEY, PM_REMOVE) Then
                [COLOR=#008000]'=========================================================
                'Comment out this line if you don't wish to activate excel.[/COLOR]
                VBA.AppActivate Application.Caption
               [COLOR=#008000] '=========================================================
                'Execute the 'Test' procedure.[/COLOR]
                Call Test
            End If
        DoEvents
    Loop
    
Oops:
    Call UnregisterHotKey(Application.hwnd, &HBFFF&)
End Sub


Private Sub Test()
    MsgBox "Procedure invoked !", vbApplicationModal
End Sub

Now, once the userform is loaded, try pressing the ALT+ 1 key combination and the 'Test' Macro should execute regardless of the currently active application/Window.

 

[GTO]

Alternatively, you can use the RegisterHotKey API which is easier to code , less costly on the system and less prone to dangerous crashes than using a system wide low level hook...

Okay, but besides those minor advantages...

Seriously, I did not know of the RegisterHotKey function. I tried it and it is 'the bee's knees'! Seems hugely safer and as you say, easier to code as well.

Thank you Jaafar; that is definitely one to save for future use

Mark