Widening the names drop-down, revisited
 

Many years ago, Chip Pearson published code to widen the drop-down list for
named ranges in the Excel Formula Bar. It widens the dropped list, but not
the static textbox.

I've revisited the code, and can now widen the text using SetWindowPos.
Unfortunately, the widened textbox starts impinging on the drop-down button,
and if widened further it eventually overwrites the formula bar.

So a complete solution needs an enumeration of all the controls on the
formula bar, so that they can be shuffled along to the right to accommodate
the widened textbox. This is a little bit beyond my API coding skills -
anyone prepared to offer a few pointers?

Here's the code:

' **** **** **** **** **** **** **** **** ****

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String _
) As Long

Private 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

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any _
) As Long

Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, _
ByRef lpRect As RECT _
) As Long

Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, _
ByVal hwndRel As Long, _
ByVal lngLeft As Long, _
ByVal lngTop As Long, _
ByVal lngWidth As Long, _
ByVal lngHeight As Long, _
ByVal lngFlags As Long _
) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Public Sub WidenNameBox()

' Nigel Heffernan

' Widen the Named Range drop-down box on the main application window.
' Adapted from code published by Chip Pearson http://www.cpearson.com

' Call WidenNameBox from the Workbook_Open event procedure

' REQUIRED API DECLARATIONS:
' FindWindow, FindWindowEx, SendMessage
' GetWindowRect, SetWindowPos


Dim hwndApp As Long ' Excel Application Window
Dim hwndMain As Long ' Client area handle
Dim hwndCtrl As Long ' Combo box control handle

Dim rectCtrl As RECT

Dim lngLeft As Long
Dim lngTop As Long
Dim lngWidth As Long
Dim lngHeight As Long

Const CB_SETDROPPEDWIDTH As Long = &H160
Const NEW_WIDTH As Long = 350
Const SWP_NOMOVE As Long = 2

hwndApp = FindWindow("XLMAIN", Application.Caption)
hwndMain = FindWindowEx(hwndApp, 0&, "EXCEL;", vbNullString)
hwndCtrl = FindWindowEx(hwndMain, 0&, "combobox", vbNullString)


' Widen the dropped-down list
SendMessage hwndCtrl, CB_SETDROPPEDWIDTH, NEW_WIDTH, 0&

GetWindowRect hwndCtrl, rectCtrl

With rectCtrl
lngLeft = .Left
lngTop = .Top
lngWidth = .Right - .Left
lngHeight = .Bottom - .Top
End With

lngWidth = lngWidth + 5

' Widen the static textbox portion of the drop-down list
SetWindowPos hwndCtrl, 0, lngLeft, lngTop, lngWidth, lngHeight, SWP_NOMOVE

' Why does the relative position parameter for the Z-Order take zero,
and work
' just fine, but break the dropdown if given the parent window handle
hwndMain?

End Sub

' **** **** **** **** **** **** **** **** ****