Resize Excel Name Box with COM add-in

Applies To
OS:
VB:
Excel		 NT, 9x, 2000
5, 6
2000

Download the Excel Name Box Resizer COM Add-in (26 kb)
Download the Source Code for the program (45 kb)

This program uses a lot of Windows API calls. Use it to learn on how to change screen elements on other programs, enumerate both parent and child windows, resize the width and the height of the combo box (in another program, no less) and how to create COM Add-ins for Office 2000 family of products.

BTW, this software requires Excel 2000 - does not work on Excel 97. Sorry to all those who asked. In VB5/6 simply can't write add-ins for that platform. However, we do have another little app that does the same thing and works in Excel 97. The catch is that you have to resize the Name Box every time you start Excel - a pain.

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.


Before After
Before Before

Excel Name Box Resizer COM Add-In

What the program does

The program does the following:

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 2000 on Windows 98.  However, there is no reason why it shouldn't work on other platforms.  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

Installation instructions

  1. Download the Excel Name Box Resizer Add-in and unzip into some folder (how about C:\Windows\Application Data\Microsoft\AddIns or C:\Program Files\Excel Name Box Resizer)
  2. If you already have the COM Add-Ins command on the Tools menu, skip to step 7.
  3. On the Tools menu, click Customize, and then click the Commands tab.
  4. In the Categories list, click Tools.
  5. Drag COM Add-Ins from the Commands box over the Tools menu on the Excel menu bar. When the Tools menu commands appear, point to where you want the COM Add-Ins command to appear on the menu, and then release the mouse button.
  6. In the Customize dialog box, click Close.
  7. On the Tools menu, click COM Add-Ins.
  8. In the COM Add-ins dialog box click Add...
  9. Locate (in the folder where you unzipped the downloaded COM add-in) and select xlNameBoxResizer.dll

  10. Click OK.  That is it.  A dialog will come up letting you set some settings.

Uninstallation instructions

  1. To unload an add-in from memory but keep its name in the list, go to the Tools menu, select COM Add-ins and clear the check box next to its name.

  2. To remove an add-in from the list and from memory, go to the Tools menu, select COM Add-ins and  click its name, and then click Remove.  Then you can simply erase the folder where the downloaded software resides.

How to use

When installation is complete, a dialog box will come up.  It will allow you to apply whatever settings you want.  Click Close when you are done resizing.  To access the Excel Name Box Resizer the next time you start Excel, simply go to the Tools menu and select Name Box Resizer.

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