Stretching range drop down box

Tkeller

Board Regular
Joined
Jul 23, 2003
Messages
134
This probably has an easy answer, but I can not find it.

I has inherited a worksheet tht has dozens of named ranges, most of them very long and most only differing slightly (e.g. output_range_1, output_range_2). I would like to be able to see the complete name for these ranges using the excel drop down box on the tool bar above. I can not figure out how to expand the width of this box. Any help would be much apppreciated.

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Tkeller,
You can use this to widen the name box.
Put this in a standard module.
Code:
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 WidenNameBoxDrop()
    Dim Res As Long
    Const CB_SETDROPPEDWIDTH = &H160
    Const cWidth = 400 '<<<<< Set the desired width here.
    Res = SendMessage( _
            FindWindowEx( _
                FindWindowEx( _
                    FindWindow("XLMAIN", Application.Caption) _
                , 0, "EXCEL;", vbNullString) _
            , 0, "combobox", vbNullString), _
          CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub

And then you can put this in the ThisWorkbook module.
Code:
Private Sub Workbook_Open()
Call WidenNameBoxDrop
End Sub

Note: I don't remember to whom to give the credit for this, but I know it works well. :biggrin:
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Ah, thanks Greg. I shall make note of that and include kudos to him in the future.
(I figured it was maybe Ivan or JPG or someone, but Chip will do as well) :biggrin:
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top