Excel Wide-Drop


In 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:

Excel named-range drop-down, before and after

Macro Code

Here 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 Spreadsheet

In 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)

Screenshot of the example spreadsheet


Unlike 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! :-)


Version 1 (14th November 2008)

  • The first version I've put on my site. See the Credits section above for the history behind it.