ThreeWave Widen The Defined Name Box

This page describes how to widen the dropdown Defined Name box.
ShortFadeBar

Introduction

NameBox1 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.

 

 

 

SectionBreak

Windows API Functions

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

NameBox2 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

-->