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

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: