how to trap the Function Keys ?

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,596
Messages
6,143,393
Members
450,484
Latest member
ChrisMac1

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
Back
Top