Normally, right about now I would link you to a previous article about how much I hate the new Office 2007 GUI (as a means to get you caught up).... but there are just so many of them now... it would take too long.  Suffice it to say, I hate the new interface and find it extremely counter-intuitive and inefficient.... so I decided to change that.

As anyone who's spent any time looking into customizing Office 2007 knows.......... it sucks. Modifying the Ribbon is much more involved and restrictive than modifying the CommandBars, and QAT modification is a slow waste of time. The only hope (kind of) that we've been given is that whenever we use code (for Office 2000-2003) to add a button to a CommandBar, that button gets dumped into a Ribbon called "Add-Ins". Well.... this gave me an idea. Since I know how to control CommandBars quite easily... and since any CommandBars I create are gonna end up in the "Add-Ins" ribbon... why don't I just recreate the entire Menu and Toolbar system in the Add-Ins ribbon and just ignore the rest of the Ribbons.

After much toil and tears... I was able to do just that......... for the most part.

First off, I want to start by saying that this solution is very NOT elegant... I'd call it a "hack in progress". There is no GUI... nothing fancy or pretty. It can take mere minutes to implement (and once you're done setting it up, it takes seconds to run in on any other machines that you want to apply the mod to), but you'll have to get used to the GUI (or lack thereof, rather).

So here's how it works. There are two procedures. The first procedure (ListIcons) lists all of the icons from Toolbars that you specify. It creates a separate column for each toolbar. So, for example, if you tell it to list the "Worksheet Menu Bar", "Standard", and "Formatting" toolbars, you'll end up with three columns that contain a list of all the items that are in those toolbars. An additional column is always added named "All".... this column lists every other button (that exists on any toolbar) so that you can easily drag them into first three toolbar columns.

Once you make the arrangement you want (by basically dragging and dropping cells), you run the second procedure, "AddToolbars", and it creates toolbars on the "Add-Ins" ribbon based on your arrangement.

Here's a link to the Excel file. It contains all the code, sample sheets, and more detailed instructions.

Currently, this process it tailored to work for Excel 2007 or Word 2007. Regardless of which app you're modifying, the code is always run from the Excel file. If you'd like me to add more Office apps to the list, let me know.

A few issues to note:

1) Some icons just won't show up. Who knows why... it's probably some vindictive MS coder who decided he'd "stick it to" anyone who tried to use the old buttons.  But for some reason, some of the buttons (even though you include them in your list) just don't get added to the toolbar. AutoSum is one... Borders, Font, and Fill are a few others. The code runs just fine... no errors are produced... and if you loop through all the controls in the custom toolbar, it says that they're there... you just can't see or use them for whatever crazy reason. (Absurdly stupid stuff like this is actually pretty common in VBA programming, so I can't definitively pin it on the vindictive MS coder ). Anyway... if anyone can figure out what can be done to fix that, let me know, and I'll update the code.

2) Not every possible icon ends up in the "All" column. The method I'm using only lists icons that exist in a toolbar. If the icon doesn't exist in any standard toolbar, it won't end up in the "All" list (and you might think that you can't add it to your toolbar). To address this, temporarily, I have added extra code that adds certain icons to the "All" list... but I'm sure there are plenty of others that I missed. If anyone knows of a method to list all icons... period.... let me know, and I'll update the code. As it stands, you can copy the code needed to add your own icons to the All list, or you can just type the ID # and Name it into a cell (in one of the Toolbar columns) yourself.

3) The menus don't work in Word. For some reason, even though it's the same process as for Excel, the menus show up in Word, but they're just empty placeholders... there are no items in any of the menus. So that sucks. Just another road-block MS threw in the way to make Office 2007 customization such a joy.

Also, there are a couple of improvements that I want to make... if anyone knows how to do any of these, let me know, and I will add it to the process.

1) Use a real separator.

Right now, I just use a blank button to separate groups. This is because the "BeginGroup" property, which used to create a "separator line" (in 2000-2003), doesn't appear to work any more in 2007 (go figger ). If anyone can figure out how to get a "real" separator between each group of buttons (instead of a blank button), please let me know.

2) Make the Add-Ins Ribbon the default Ribbon.

When you start Excel, it always displays the "Home" ribbon by default. If anyone knows how to change it so that the Add-Ins Ribbon (and therefore, our custom toolbars) get displayed right away when Excel opens, please let me know.

3) Hide the Ribbons menu.

I really doubt this is possible, but if anyone figures out how to hide the entire Ribbon menu ("Home", "Insert", "Page Layout", etc) then let me know. Obviously, we'd first need to figure out how to make the Add-Ins ribbon the default. This would clear up space at the top, and keep us from essentially seeing two menus (the Ribbons menu and the old menu that we're adding)

Anyway... here's what my Excel 2007 Add-Ins ribbon looks like after running the customization. It's fairly close to what I'm used to and a LOT easier to work with.

WATYF