Widen The Defined Name Box
This page describes how to widen the dropdown Defined Name box.
If you use defined names in your workbooks, you are likely frustrated by the fact
that the Defined Name box isn't wide enough to display long names. It truncates
names at about 16 characters. As you can see in the image of to the right, you can't
see the differences between the two long names.
While it is not possible to widen the name box itself (Excel 2007 allows this, but earlier versions
do not), it is possible to expand the width of the drop down list. With a few calls to Windows API functions, we can widen the drop down box.
The code shown below will widen the drop down list of the Name box. Paste the code into a standard module. If you put the code
in a class module such at the ThisWorkbook module, you must change the declaration from Public to
Private since classes cannot contain Public Declare statements.
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
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
Sub WidenNameBoxDrop2()
Dim Res As Long
Const CB_SETDROPPEDWIDTH = &H160
Const cWidth = 400 '<<<<
Res = SendMessage( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString), _
CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub
In the code above, change the line marked with <<<< to the width, in
pixels, that you want to drop down to be. In this example, it is set to 400 pixels.
You should choose a size the fits your monitor and screen resolution. As
you can see in the image to the right, the drop down is wide enough to display the
complete defined names.
This page last updated: 29-July-2007