Excel Toolbars for Fun & Profit
Posted by Alan Barasch on July 14th, 2007
I started a new job several years ago where the first thing they told me was that they needed a custom Excel utilities toolbar. Like any good developer, I asked them what tools they had in mind … I got a blank stare.The following toolbar is the result of many revisions.
I found a utility on John Walkenbach’s site to get the face ids for the icons on the toolbar.
The tools are what we needed to manipulate data to prepare for further processes. The first company where I worked on this handles lots of UPC codes; some items might have 5 different codes depending on the configuration of the package.
We received a lot of data as text and even after spreading it out, it needed my “Mass Text to Columns†to make the numbers be numbers.
Some tools are more useful than others. The “Delete T:U†was only useful in one type of spreadsheet; but someone wanted it. “Landing Zone†came about because we delivered a 30 tab update every morning. The manager wanted every page to open to cell A1.
“Color Things†is the most popular tool. The background of my example is the result of multiple colored columns. (A client actually wanted every column to be a different color).
Each new assignment gives me the chance to add something new and interesting.
The code for building this toolbar is on my site and in a text file listed below. The code for building the individual utilities is scattered all over my site.
As you can see, I have added the toolbar to my Excel 2007. I have tested most of the tools and they continue to work. Considering that I started this exercise in Excel 97, that is extraordinary.
 The image at right is the “Help Screen†for my toolbar.
If you build a toolbar, put it in the Excel Object, ThisWorkbook.
The Goodies
Enjoy!