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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Board Regular
Joined
Mar 19, 2002
Messages
88

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
2,916
I think you need to use the curly brackets {}, not parentheses().
 

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

Forum statistics

Threads
1,144,221
Messages
5,723,102
Members
422,477
Latest member
pete101

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