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:
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
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.
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:
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)