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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When working with colors in Excel you can specify them using either RGB or HSL values. Converting from HSL to RGB can be ...
Discover MoreSometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can ...
Discover MoreYou may want your macro to clear the clipboard so that people cannot access anything left in the clipboard. That is ...
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