Thread: Capturing Keys

How do I capture keys sent to Excel when my workbook is open / active?

I know when you write a macro you can assign a shortcut key combo to it, such as "Ctrl-Shift-s", but I can't find where / how Excel stores that information, and can only presume it is stored away not accessible to the coder, (ie. not by going via the macro -> properties boxes).

My issue is I want to capture a key combo, then from that call a function passing a variable dependant on the combo pressed, eg:

Code:

Function DispMessage(themessage As String)
MsgBox themessage
End Function

Code:

xxxxxxxxx
Capture Ctrl-Shift-s
DispMessage "Ctrl-S was pressed"
xxxxxxxxxxxxxxx
Capture Ctrl-Shift-b
DispMessage "Ctrl-B was pressed"

Thanks

It is stored in the actual code, look at the recorded macro in the VB Editor

Code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/10/2009
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    ActiveCell.FormulaR1C1 = "I pressed Ctrl-Shift-S"
    Range("A2").Select
End Sub

I understand that Roy, but I can remove that comment line, and press Ctrl-Shift-S and the macro will run again, so it is not THAT that is controlling which macros are run by which key combos.

I shall D_Rennie's code, thanks for that

Originally Posted by D_Rennie

Would you be able to use the .onkey

E.G

Code:

Sub SetShortcutKeys()
With Application
.OnKey Key:="^+K", Procedure:=ScreenPaste
End With
End Sub

I tryed just the other night to assign a shortcut key to a macro in xl2007 and couldnt figure it out. It seams diffrent that what i found on the net.

Cheers, I had to put quotes around the procedure name, but other than that it works very well

Code:

.OnKey Key:="^+K", Procedure:="ScreenPaste"

thanks again!

I don't belive you can access the information via the OM.

You would need to export the module to a .bas file and then read the text file to discover what values have been set for VB_Invoke_Func

Macro1 has the CTRL+k shortcut.

Code:

Attribute Macro1.VB_ProcData.VB_Invoke_Func = "k\n14"

Originally Posted by D_Rennie

Here is what keeped popping up for me in the google searches last night.

select.. Tools,,, Macros, Select the macro,, Push buttion macro options. There will be the assigned macro short-cut key.

Though for the life of me i that options buttion is not there, Nore anywhere in the Editor.

That is certainly the way to do it manually in 2003, I suppose it is another thing that has changed in 2007 though...

for xl2007 check the Developer tab. The Macro button displays the old dialog with Options available.

Oops, my bad, it doesn't do what I was after, as it will still only let me assign a procedure to it that doesn't take arguments

As an example, lets say I want ctrl-shift-a to perform 12+5 and display a message box, and ctrl-shift-s to perform 12-5 and display a message box.

I have a procedure:

Code:

Sub DoTheMath(addition As Boolean)
If addition Then
  MsgBox 12+5
Else
  MsgBox 12-5
End If

I was wanting someway to write the code that would allow the key-combo ctrl-shift-s to call "DoTheMath False", but there doesn't seem be anything.

I know I could have 2 seperate 'helper' subs, as below, but that pretty much defies teh point of what I was trying to do:

Code:

Sub DoAdd()
' Shortcut ctrl-shift-a
DoTheMath True
End Sub

Sub DoSub()
' Shortcut ctrl-shift-s
DoTheMath False
End Sub

Seeing as you are assign to sets of keystrokes I can not see why separate routines is a problem.

You will not be able to pass an argument if you are using keyboard shortcuts.