Modifying the Ribbon in Excel 2007

Posted on March 27, 2008

I have recently found a need to modify the Ribbon interface in Excel 2007. I thought it would be much harder than it actually is. Rather than going through a tutorial of my own, I thought that I would post some excellent Ribbon modification tools and tutorials.

Before doing this, you need to understand that Office 2007 files are really just archives (.zip files) of several XML files. So, if you want to explore the file you can just change the file extension to .zip and then extract the files from the archive. Windows Explorer can do this. Open Windows Explorer, find your file, change the extension, and then right-click it and choose Extract All from the menu.

You will also need to know the names of the controls on the Ribbon, and what type of control they are, in order to change them. Fortunately, Microsoft provides a list of all of them in a file available on Jensen Harris’ blog or on this Microsoft Download Center page.

Ribbon Modification Tools

I’m aware of three tools that make modifying the Ribbon very easy. Of course, you can do it manually if you know how to to insert a custom XML file into an Excel 2007 file, and how to set the relationships properly. However, these tools will make your life much easier:

The Office 2007 Custom UI (free!) editor from OpenXMLDeveloper.org. This is a fairly barebones tool, but it is the one that I’m using. It is basically a text editor where you type in your XML (known as RibbonX code) to apply to the Ribbon. To use it, just open an existing Excel 2007 file (it can be a blank file that you just saved, or even an Excel 2007 template file so that you can reuse it for many other files) and then enter the XML. When you save the file, the Custom UI editor will save the changes into the file and make sure that the relationships (the .rels files inside the Excel file) are set correctly. It will also check your XML to make sure that it is valid.
Mike Alexander has posted a worksheet-driven ribbon customizer (free!) on his blog at DataPig Technologies (you’ve got to love the name of his company). This is a very easy to use Excel 2007 workbook. Download it, fill in a few cells, and your Ribbon will be modified automatically. This is probably the easiest way to get started.
Ribbon Customizer is available as a free starter edition or in a $29.99 Professional version. I haven’t yet tried this one, but it looks to be excellent and very easy to use. With the free trial, you really can’t go wrong.

Ribbon Modification Tutorials

When I first looked at modifying the Excel 2007 user-interface, I quickly found some tutorials on the Microsoft Developer Network (MSDN). These tutorials provide some good information that you should eventually read, but it seems to be aimed primarily at professional developers using Visual Studio Tools for Office (VSTO).

The very best tutorials that I have found were written by Nick Hodge over at Excel User Group. Part 1 of his tutorial gives some good information about the new XML-based file format, and then parts 2 and 3 dive into customizing the Ribbon using the CustomUI editor that I mentioned above. Part 4 gets into some of the other controls that you can use. Supposedly, there is a part 5 coming someday to wrap up the series. These tutorials are very readable and I highly recommend them

Ron de Bruin has a shorter tutorial with some downloadable examples available at his site.

Books that Cover RibbonX

There are at least three books that I know of that are dedicated to, or at least cover, the Ribbon:

John Walkenbach has updated his “Power Programming” book to Excel 2007. Walkenbach has written several great books on Excel over the years, and Excel 2007 Power Programming with VBA is no exception. This book is a must have for anybody who writes Excel VBA. Chapter 22 covers working with the Ribbon. I very happily own this edition, as well as the last several.
RibbonX: Customizing the Office 2007 Ribbon is written by three Microsoft MVPs and looks to be very good. I’ll buy this one soon.
RibbonX For Dummies is also available, and it is dirt-cheap ($6.00) at Amazon as of this writing.

I hope that you find these resources to be useful. Customizing the Ribbon actually turns out to be fairly painless once you understand how to do it.

Posted by on March 27, 2008 at 02:52 AM

Categories: Excel Excel 2007 Ribbon

Comments:

Thanks for the good information. But I think 2 considerations should be taken before customization:
If you make mistakes, or you don’t like the changes you’ve made, is it easy to undo your changes? You should save the files for backup, in case you want to get back to the initial settings.

Second - dont get too accustomed to your new settings, or you will have difficulty finiding the buttons on any other computer than your own....

But overall - it seems a very small but significant step of Microsoft pointing towards a sort of an “Open source” software.

_____________
Excel 2007 tutorial

Posted by Jack Gopher on April 18, 2008 at 02:00 PM | #

Jack,

As for getting back to your original Ribbon settings, it couldn’t be easier.

First, remember that the customizations only apply to the active workbook. So, if you open a new workbook with no RibbonX customizations the Ribbon is exactly like it was when you first installed Excel 2007. In fact, if you have a modified workbook and an unmodified one open, the Ribbon will change when you switch between the files.

Second, if you open the file in the CustomUI editor and delete your XML, then the Ribbon goes back to normal. So, you really can’t hurt anything.

The only way to make “permanent” changes to the Ribbon is to install an add-in that has modifications. Even then, disabling the add-in will cause the Ribbon to return to normal. There is no possibility of left-over modifications hanging around like there was with the old-style custom toolbars.

Posted by on April 18, 2008 at 06:13 PM | #