how to trap the Function Keys ?

[Billm]

Aswell as clicking a button on the spreadsheet, my users want to be able to press one of the Function Keys or a Control-Function Key to achieve the same thing. So I thought of using a Macro that responded to a Function Key but I couldnt get it to run the Sub button_click() code that the button runs.

Is there another way to trap when a user presses F8 or Control-F8 and then call this sub ?
Thanks Bill

 

[jimboy]

Bill,

I don't think you can have a shortcut using the F keys, but if you press Alt-F8 and select the macro and "Options..." you can assign a shortcut using letters/numbers.

Hope this helps.

 

[dk]

Hello,

You can set it up by using the Onkey method.

E.g. to set up F8 so that it runs your procedure run this:-

<pre>
Sub SetItUp()
Application.OnKey "{F8}", "ProcedureName"
End Sub
</pre>

If you wanted it to be Ctrl F8 just put a caret ^ sign in in front of the first brace.

Before the workbook closes you should reset F8 back to nothing e.g.

<pre>
Sub RemoveIt()
Application.OnKey "{F8}", ""
End Sub
</pre>

If you don't do this then Excel will still try and find the button_click macro, even if the workbook containing it is closed.

 

[Joe Was]

You can assign a Macro Option key (Hot-key) to the macro code under Macro-Macros(select)-Options, then add your hot key.

The hot-key option must start with the Ctrl-key, like:

Ctrl-a

When the hot key "Ctrl+a" is hit the macro you assigned it to will run as a key-board short-cut. JSW

P.S. I do not know of a way to assign a macro to a function key, but will look into it some more. JSW

 

[Billm]

DJ, I added this code to a blank worksheet to test it:

Sub onkey_F9()
Application.OnKey "(F9)", "test1"
End Sub

Sub test1()
MsgBox "hello"
End Sub

I pressed the F9 key but it didnt work. What have I done wrong ?
Thanks
Bill

 

[Seti]

I think you need to use the curly brackets {}, not parentheses().

 

[Billm]

Hmm .. I replaced the (F9) with {F9} ie curly brackets ..and it still sits there and looks at me when I press the F9 key .. lol

 

[dk]

Hi,

Firstly, yes you need to use curly brackets. Secondly, you need to run the procedure onkey_F9 first manually to initialise the F9 key. You would typically do this in the workbook's open procedure.

Once you run onkey_F9 you should be able to press F9 and it will run test1.

 

[Billm]

Dan, ok I changed to the curly brackets and manually ran the code to initialise it.

I now get an error message when I press F9 warning me that the Macro Test1 does not exist/cannot be found. Does this code call Macros only. As you can see, my Test1 sub is just a simple sub and not a macro.

Ultimately, what i am wanting to do is run an existing Sub via the F9 key.
Thanks for your help
Bill

 

[dk]

Where does the procedure Test1 reside? It should be in a normal module. There isn't any difference between a macro and a procedure so it won't be that. If you're still stuck then you can email a test workbook to dklann@lineone.net