Excel Ribbon Customization

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Excel 2007 offers its very own way of customizing the Ribbon, which is far more complex than manipulating the CommandBars collection. The new approach involves writing XML code in a text editor, copying that XML file into the workbook file (all outside of Excel!), editing a bunch of XML files (which also are stashed away inside the new Excel file format, which is really nothing more than a zipped container of individual — but related — files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file.

Explaining all the intricate details involved in customizing the Ribbon is well beyond the scope of this book. However, I walk you through a quick example that demonstrates the steps required to (manually) add a new Ribbon group to the Home tab. The new Ribbon group is named Excel VBA For Dummies, and it contains one button, labeled Click Me. Clicking that button runs a VBA macro named ShowMessage.

1. Create a new Excel workbook, insert a VBA module, and enter this procedure:

Sub ShowMessage(control As IRibbonControl)

MsgBox "Congrats. You found the new ribbon command."

End Sub

2. Save the workbook, and name it Ribbon ModificationModification.xlsm.

3. Close the workbook.

4. Activate the folder that contains the Ribbon Modification.xlsm file and create a folder named customUI.

5. Inside of that folder, use a text editor (such as Windows Notepad) to create a text file named customUI.xml, with the following XML code:


xmlns='http://schemas.microsoft.com/office/20 0 6/01/customui'>


<tab idMso='TabHome'>

<group id='Group1' label='Excel VBA For Dummies'> <button id='Button1' label='Click Me'

size='large' onAction='ShowMessage' imageMso='FileStartWorkflow' /> </group> </tab> </tabs> </ribbon> </customUI>

6. Open Windows Explorer and locate the Ribbon Modification.xlsm file.

7. Add a .zip extension to the file by pressing F2 and then changing the filename to Ribbon Modification.xlsm.zip. This way, you will be able to edit and view the actual contents of the Excel file by using your favorite file compression program. This includes adding/removing files to the zip container.

8. Drag the customUI folder you created in Step 4 into the Ribbon Modification.xlsm.zip file. (Windows treats ZIP files as if they were folders.)

9. Every Excel file (in the new file format) has a folder named rels. Double-click the rels folder within the ZIP file. This folder contains one file, named .rels.

10. Drag the .rels file to a location outside of the ZIP file (your Desktop, for example).

11. Open the .rels file (which is an XML file) with a text editor, such as Notepad.

12. Add the following line to the .rels file, before the </Relationships> tag:


Type="http://schemas.microsoft.com/office/2 00 6



13. Drag the .rels file back into the ZIP file, overwriting the original version.

14. Remove the .zip extension so that the file is back to its original name: Ribbon Modification.xlsm.

If all went well, you see the new Ribbon group (Excel VBA For Dummies) when you open the workbook. If you're lucky, your screen will look like Figure 19-3.

Scary stuff, eh? No one should actually have to go through these steps. Remember, this book was published when Excel 2007 was just released. By the time you actually read this book, tools should be available to simplify this daunting task. Try searching the Web for office custom UI editor.

Adding a button to the Quick Access Toolbar

If you create a macro that you use frequently, you may want to add a new button to the Quick Access Toolbar (QAT). Doing so is easy, but you must do it manually. The QAT is intended to be customized by end users only — not programmers. Here's how to do it:

1. Right-click the QAT and select Customize Quick Access Toolbar to display the Customize tab of the Excel Options dialog box.

2. In the drop-down box labeled Choose Commands From, select Macros.

3. Select your macro from the list.

4. Click the Add button, and the macro is added to the QAT list on the right.

5. If you like, click the Modify button to change the icon and (optionally) the display name.

Unfortunately, the new QAT button only works when the workbook that contains the macro is open. Even worse, the macro works only when that workbook is the active workbook.

In order to run a macro from the QAT when any workbook is active, store the macro in your Personal Macro Workbook.

Figure 19-3:

A new command in a new Ribbon group.

_ n x

® - a x


¡2 Cleai -

SortBi Find & Filter T Select ■r

CPrck Me



Excel VBA Far Dummies











But even with a good tool to help, you still need to understand XML. If that sounds appealing to you, check the bookstores. I suspect that there will be many books devoted exclusively to customizing the Ribbon interface in Office 2007. This isn't one of them.

You can download a sample file from this book's Web site, which contains this customization. If you want to take a look at the internals, rename the file by appending .zip to its name. Then you can open the file in any popular unzip program, such as WinZip or even in Windows XP or Vista, which has native support for zip files. The file containing the XML I showed you above is called "customUI.xml".

Because this XML stuff is way too complex for the beginner VBA programmer, the remainder of this chapter focuses on UI customization that uses the old method (VBA only). It's not as slick as the Ribbon, but it's a lot easier, and still provides quick access to your macros.

Working with CommandBars

A CommandBar object is used for three Excel user interface elements:

1 Custom menus 1 Custom toolbars

1 Customs shortcut (right-click) menus

In Excel 2007, the CommandBar object is in a rather odd position. If you write code to customize a menu or a toolbar, Excel intercepts that code and ignores many of your commands. As I've noted earlier in this chapter, menu and toolbar customizations end up in the Add-InsOMenu Commands or the Add-InsO Custom Toolbars group. So, for all practical purposes, you're limited to the shortcut menus.

In this section, I provide some background information that you simply must know before you start mucking around with CommandBars.

Was this article helpful?

0 -1

Post a comment