Written by Allen Wyatt (last updated April 27, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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, 2021, and Excel in Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
One of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this ...
Discover MoreOne common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you ...
Discover MoreWant to get rid of all the zero values in a range of cells? This tip provides a couple of different ways you can ...
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 © 2025 Sharon Parq Associates, Inc.
Comments