Pearson Software Consulting Services

    Defined Name Box Shortcut     

         Excel provides many shortcut keys, but one that is sorely missing is a shortcut to the Name Box on the formula bar. However, you can use the following macro to set the focus to the defined name box.  Assign a shortcut keystroke to the following macro (I use CTRL+SHIFT+N).
 

 

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 SetFocusNameBox()
    Dim Res As Long
    Res = SetFocus( _
        FindWindowEx( _
            FindWindowEx( _
                FindWindow("XLMAIN", Application.Caption) _
                    , 0, "EXCEL;", vbNullString) _
                    , 0, "combobox", vbNullString))
End Sub
 

This code has been tested and it does work in Excel 2007.

On a related note, you may find it annoying that the Name Box only the first 16 or so characters of a defined name. This may cause long and similar names to appear the same in the Name Box.  As a remedy to this dilemma, you can widen the Name Box when it drops down by using the code here.
     
     

 

Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New    Links   Legalese And Disclaimers
chip@cpearson.com

© Copyright 1997-2007  Charles H. Pearson