| 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 |
![]() |
![]() |
The program does the following:
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.
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
Click OK. That is it. A dialog will come up letting you set some settings.
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.
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.
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.
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.
If you are having problems with this program, send email to rgelb@vbrad.com
Source code for this is located at http://www.vbrad.com