Edit in Formula bar instead of cell

CA CS CIMA Prakash Somani (Landmark Group) (23512 Points)

18 January 2011

While many, if not most, users have the "Edit directly in cell" option turned on, and press F2 to edit the contents of a cell, some users still prefer to edit within the formula bar. If "Edit directly in cell" (Tools menu, Options, Edit tab, "Edit directly in cell") is UNchecked, pressing F2 will set the input focus to the formula bar, as expected. However, if "Edit directly in cell" is checked, and you need or want to edit within the formula bar, there is no built-in shortcut key to take you to the Formula bar -- you must use the mouse. The following macro will set the focus to the formula bar. You can then assign a keyboard shortcut to run this macro, thus giving you a keyboard shortcut to the formula bar.

Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Sub SetFocusFormulaBar()
    SetFocus FindWindowEx(FindWindow("XLMAIN", Application.Caption), _
        0&, "EXCEL<", vbNullString)
End Sub