Written by Allen Wyatt (last updated April 27, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
When someone is using Lorenzo's workbook, he would like only the F2 key to be accessible to the user. He wonders if there is a way to disable all the function keys in Excel, with the exception of the F2 key?
There is a way, but it must be done using macros. Specifically, you'll want to use the OnKey method of the Application object. This code will disable all the function keys with the exception of F2:
Private Sub Workbook_Open() Application.OnKey "{F1}", "" Application.OnKey "{F3}", "" Application.OnKey "{F4}", "" Application.OnKey "{F5}", "" Application.OnKey "{F6}", "" Application.OnKey "{F7}", "" Application.OnKey "{F8}", "" Application.OnKey "{F9}", "" Application.OnKey "{F10}", "" Application.OnKey "{F11}", "" Application.OnKey "{F12}", "" End Sub
The macro must be added to the ThisWorkbook module and it will run automatically when the workbook is opened. If desired, you could make the macro a bit shorter:
Private Sub Workbook_Open() Dim sTemp As String Dim J As Integer For J = 1 to 12 If J <> 2 Then sTemp = "{F" & Trim(Str(J)) & "}" Application.OnKey sTemp, "" End If Next J End Sub
You'll want to also make sure that when you close the workbook that you re-enable all the function keys. This macro, again, should be added to the ThisWorkbook module:
Private Sub Workbook_BeforeClose(CANCEL As Boolean) Application.OnKey "{F1}" Application.OnKey "{F3}" Application.OnKey "{F4}" Application.OnKey "{F5}" Application.OnKey "{F6}" Application.OnKey "{F7}" Application.OnKey "{F8}" Application.OnKey "{F9}" Application.OnKey "{F10}" Application.OnKey "{F11}" Application.OnKey "{F12}" End Sub
You could also use a shorter version of the same code:
Private Sub Workbook_BeforeClose(CANCEL As Boolean) Dim sTemp As String Dim J As Integer For J = 1 to 12 If J <> 2 Then sTemp = "{F" & Trim(Str(J)) & "}" Application.OnKey sTemp End If Next J End Sub
There are a couple of caveats to using these macros. First, realize that the only thing that is disabled are the function keys themselves. Variations on the function keys that use combinations of Shift, Alt, and Ctrl are not affected. Second, once the function keys have been disabled, they are disabled for all workbooks you may have open at the time.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13523) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you record a macro, make sure that you know how Excel is recording your cell movements. This tip explains the ...
Discover MorePart of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how ...
Discover MoreWhen writing a macro, you may want to fill a range of cells with different values. The easiest way to do this is to use ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-04-27 11:37:07
J. Woolley
The Tip's macros include the following statement:
sTemp = "{F" & Trim(Str(J)) & "}"
VBA's Str(Number) function returns a String (text) representation of Number (numeric). A leading space is always reserved for the sign of Number. If Number is positive, the returned string contains a leading space and the plus sign is implied. Therefore, Trim(Str(J)) is necessary. However, the following statement is simpler because the & operator automatically converts numeric values to text:
sTemp = "{F" & J & "}"
2024-04-27 06:17:08
Mike J
If you wish to disable/re-program the Shift/Control/Alt versions, just precede the codes in this tip with:
Shift + (plus sign)
Ctrl ^ (caret)
Alt % (percent sign)
For a complete list of codes that can be controlled in this way, see:-
https://learn.microsoft.com/en-us/office/vba/api/excel.application.onkey
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments