Excel Wide-DropIntroductionIn Excel, on the left of the formula bar, there is a drop-down list for the named ranges in the current sheet. In versions before Excel 2007 this list is ridiculously small, both in width and height. This can cause big problems when editing sheets with a lot of ranges. The VBA macro provided below will resize the drop-down to something larger and more useful. You can edit the macro to change the size if you wish. Change the numbers 200 and 600 in the last two lines, ignoring the "End Sub" line. Warning: This macro makes the drop-down larger by directly resizing Excel's private windows. This is not supported by Microsoft and could cause something to break. I have used it a lot it without seeing any problems but your experience may be different. If you want to use the macro then I recommend binding it to a button in your sheet which you can click manually rather than setting it to run automatically when your sheet is opened. That way if anything goes wrong you can simply stop clicking the button. Excel 2007 (and above): If you're using Excel 2007 or above then you don't need this macro and it won't actually do anything anyway. It seems that Microsoft realised there was a problem and finally, after all these years, made it possible to resize the named-range field with the mouse in Excel 2007. Just point to the gap between the field and the formular bar and you'll see a resize cursor, then click and drag to the right. This will resize both the toolbar field and the drop-down (unlike my macro which only resizes the drop-down). Here's a screenshot showing the drop-down, before and after:
Macro CodeHere is the VBA macro code. The function to run is WideDrop right at the end. That's what I bind the button in my sheet to. If you are familiar with Win32 then the code should be obvious to you. If not then here's a quick explanation: It finds the drop-down window control, resizes it horizontally (sending it a CB_SETDROPPEDWIDTH message) and then resizes it vertically (by resizing the window itself). (Drop-down controls are a bit weird since they have two sets of dimensions, one for the collapsed control and one for the expanded drop-down list.) Private Type W32RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Private Type W32POINT
x As Long
y As Long
End Type
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 SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, ByVal cX As Long, ByVal cY As Long, ByVal wFlags As Long) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As W32RECT) As Long
Private Declare Function ScreenToClient Lib "user32" (ByVal hwnd As Long, lpPoint As W32POINT) As Long
Private Function ScreenToClientRect(ByVal hwnd As Long, ByRef lpRect As W32RECT) As Long
Dim p1 As W32POINT
Dim p2 As W32POINT
p1.x = lpRect.Left
p1.y = lpRect.Top
p2.x = lpRect.Right
p2.y = lpRect.Bottom
ScreenToClientRect = ScreenToClient(hwnd, p1)
ScreenToClientRect = ScreenToClient(hwnd, p2)
lpRect.Left = p1.x
lpRect.Top = p1.y
lpRect.Right = p2.x
lpRect.Bottom = p2.y
End Function
Private Function MoveAndResizeWindow(ByVal hwnd As Long, ByVal hwndParent As Long, _
x As Long, y As Long, w As Long, h As Long) As Long
Dim rct As W32RECT
Const cSWPFlags = 20 ' 20 = SWP_NOACTIVATE|SWP_NOZORDER
MoveAndResizeWindow = GetWindowRect(hwnd, rct)
MoveAndResizeWindow = ScreenToClientRect(hwndParent, rct)
If (x <> 0) Then rct.Left = x
If (y <> 0) Then rct.Top = y
If (w <> 0) Then rct.Right = rct.Left + w
If (h <> 0) Then rct.Bottom = rct.Top + h
MoveAndResizeWindow = SetWindowPos(hwnd, 0, rct.Left, rct.Top, rct.Right - rct.Left, rct.Bottom - rct.Top, cSWPFlags)
End Function
Public Sub WideDrop()
Dim Res As Long
Dim WndExcel As Long
Dim WndBar As Long
Dim WndCombo As Long
WndExcel = FindWindow("XLMAIN", Application.Caption)
WndBar = FindWindowEx(WndExcel, 0, "EXCEL;", vbNullString)
WndCombo = FindWindowEx(WndBar, 0, "ComboBox", vbNullString)
Res = SendMessage(WndCombo, 352, 200, 0) ' 352 = CB_SETDROPPEDWIDTH
Res = MoveAndResizeWindow(WndCombo, WndBar, 0, 0, 0, 600)
End Sub
Example SpreadsheetIn case it is useful I have provided an example spreadsheet with the button already hooked up to the macro. Note that downloading Excel spreadsheets, especially ones containing macros, can be dangerous. I have signed the zip file with PGP to confirm it comes from me and has not been tampered with but if you have no way of verifying the signature then be careful. It's always possible that someone hacks my web server and puts up somethig malicious. Of cource, they could also change the text of the macro above but that should be easier to notice. ExcelWideDrop_v1.zip (27k) (PGP signature)
CreditsUnlike most things on my site I cannot claim credit for this beyond some extremely minor changes. I started using the macro so many years ago that I cannot remember for sure where it came from but a web search for "CB_SETDROPPEDWIDTH Excel" shows it was almost certainly this usenet post: microsoft.public.vb.winapi: Manipulating windows and controls in Excel by Nile Hef. That post, in turn, was based on this slightly simpler macro (which widens the drop-down but does not make it taller): Widen The Defined Name Box by Pearson Software Consulting, LLC I'm do not claim credit for the implementation or the idea. I'm just putting it here because I find it useful and hope others may find it here instead of not finding it at all! :-) HistoryVersion 1 (14th November 2008)
|
| Contents // Excel Wide-Drop |  |
| |
 |
