How to change the dimensions of the Excel Name Box	Applies To
	OS: VB: Excel	NT, 9x, 2000 5, 6 97, 2000

Download the Excel Name Box Changer
Download the Source Code for the program

This program is written in hard core API. Use it to learn on how to change screen elements on other programs, emulate Spy++, resize the width and the height of the combo box (in another program, no less). Below is a description of the program - basically the help file :) that comes with the program. Check out the before & after shots of Excel. This convenience of seeing more data in one shot has saved me a lot of time.

Update: COM Add-in for Excel 2000 that does the same thing is now available.

Before After

Before Before

Name Box Dimension Changer

What the program does

The program does the following:

It allows user to resize the Name Box.
It resizes the width and the height of the dropdown portion of the Name Box. This allows the user to see the assigned name entries in their entirety and see more of them at a time.

Why the program was created

A while ago I was helping my wife with her accounting project. She was working in Excel and because the project was very complicated, she relied on me to write various macros. Having never really worked in Excel, the first thing that drove me nuts was that the Name Box could not be resized. The spreadsheet used many assigned names and I had to awkwardly scroll horizontally in the Name Box to see them in the entirety. Yet another thing that annoyed me is that when I dropped down the Name Box I could only see 7 entries - totally inadequate for a large spreadsheet.

So out of frustration I cranked up this program. The source code is available for anyone to use.

Requirements

The program has been tested with Excel 97 on Windows NT and Excel 2000 on Windows 98. Runtime for Visual Basic 6 is also required. Most likely, you already have it. If you don't, the runtime is available at www.shareware.com or www.download.com

How to use

Simply follow the steps. Step 2 is potentially confusing. Simply move the mouse pointer over the Name Box in Excel. That is all. If you do it right (how can you not), a dialog box will pop up alerting you to that.

Keep in mind that the changes you bring upon the Excel Name Box are not persistent. In other words, if you quit Excel and start it again, the Name Box will go back to its default size. I understand that this is an annoyance, but there really isn't any other way to do this (that I know of).

Note of caution

Don't go crazy with the number of Lines to show in the dropdown. Because Excel wasn't designed to show more that 7 lines, it will act wacky if forced to show an overly large amount of lines. Generally, keep it to 32 lines or less for 1024x768 or 800x600 screen resolution. Adjust the number of lines to your screen resolution. If you do force Excel to show more than your screen real estate can handle, it will retaliate by making you select from the Name Box without letting go of your mouse (a la using menus in Windows 3.1). Not to worry. Simply reduce the number and click Resize again.

Troubleshooting

If you are having problems with this program, send email to rgelb@vbrad.com

Source Code

Source code for this is located at http://www.vbrad.com