Widening the Name Box -- need help

[John Wirt]

I need to widen the Name Box in Excel 2000.

I was adviised on these pages to visit Chip Person's web page
(http://www.cpearson.com/excel/NameBox.htm). His technique for doing so is
to create a "regular" module in the PERSONAL.xls workbook and put the code
below in it."

Then the subroutine, WidenNameBoxDrop2, is called from a Workbook_Open
procedure inserted in the "This Workbook" module of PERSONAL.xls.

I've done all this and nothing happens when I open a new workbook. What's
wrong?

Even if I step through the WidenNameBoxDrop2 procedure, nothing happens.

John Wirt

- - - - - - - - - - - - - - -
PEARSON'S 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 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

 

[Norman Jones]

Hi John,

The box (or, rather, the dropdown display) should widen when the dropdown
arrow is clicked. Is this not happening for you?

 

[Bob Phillips]

John,

It won't happen when you open a new workbook, but rather the next time you
start Excel (and open Personal.xls).

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

John

An alternative to Chip's code...........

Robert Gelb has a free COM add-in that does this.

http://www.vbrad.com/pf.asp?p=Source/src_xl_nb_addin.htm


Gord Dibben Excel MVP