Friday, 24 February 2017
Main Menu
Web Links
Blog Links
Musical Links
Nerdery Links
Other Neato Links
Getting back the old GUI in Office 2007.... sort of.
User Rating: / 9
Written by WATYF on Thursday, 14 December 2006 (23307 hits)
Category: Nerdery

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. Tongue out 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. Tongue out

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. Tongue out 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 Tongue out). 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. Tongue out


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 Tongue out). 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.




< Prev   Next >


You must javascript enabled to use this form

Hi, I was wondering if you could help me to get this macro started, im not sure how to, when I open up the file I can't find where to start the macro as office 07 is so new to me.


Posted by Jason, on 02/13/2007 at 23:24

Well, now that I don't have 2007 installed, I can't quite remember where it was at, but on one of those Ribbons there should be a macro icon that will pull up the list of macros available in the workbook. There's also a way to add a 'Developer' ribbon (somewhere in the main Excel Options). If that doesn't work, you can always just hit Alt-F11 to bring up the VBA editor, then put your cursor in one of the procedures and hit F5 to run it. WATYF

Posted by WATYF, on 02/14/2007 at 07:56

Hi again, thanks to the info last time, I managed to get the macro running in Excel, but I'm not too sure on how to run it in word.
I have changed the SApp variable to word, but don't know what to do know next. Your help would be much appreciated.


Posted by Jason, on 02/14/2007 at 22:25

You just run the same two macros again. Change the variable to 'Word' and then run ListIcons. It will list all of the standard icons for Word. Then you rearrange them (optional) and run AddToolbars. This time, it will add the Toolbars to Word, instead of Excel.

Remember that the File/Edit/View/etc meuns don't appear to be working in Word. I don't know why, and I didn't get a chance to work that out before I got rid of 2007.


Posted by WATYF, on 02/15/2007 at 08:18

Seriously, thanks this is awesome. I got so fed up with constantly switching between tabs. I actually started using keyboard macros like I did in the 90's because it is so much quicker than the bulky and scattered ribbons. You saved me a lot of frustration.

Posted by Dan, on 12/17/2008 at 11:02

This is incredible!!!
I tried to out once before and couldn't get it to work. A few months later I go so fed up with Excel 2007 layout I decided to give it a try.
I read the instructions carefully and ran the macro. I didn't realize it made a new ribbon at first, but once I did, you just made my year!!
I absolutely love this. It gives me the option to try and find how to do things in 2007 but when I get frustrated, I just go to the 'add-ins' ribbon and do it the old fashioned way!
Thanks so much for this!!

Posted by Happy, on 01/21/2009 at 08:36

I realy love this macro, but I have a problem when running this with word. I get a debug error saying: Invalid procedure call or argument. It's on this line:
For Each cmd In oApp.CommandBars(Trim(bars(x))).Controls

Any help on this would be appreciated.

Thanks Julien.

Posted by Julien Moorrees, whose homepage is here on 02/19/2010 at 02:36

Actually has released an add-in named Classic Menu for Office which can bring the exactly same menus and toolbars of Office 2003 into Office 2007/2010 Ribbon. It make Office 2007/2010 look like Office 2003.

Posted by Jolie, on 09/21/2011 at 20:45

Page 1 of 1 ( 8 Comments )
©2007 MosCom


© 2017 Musical Nerdery
Joomla! is Free Software released under the GNU/GPL License.