The Office 2007 Ribbon is a huge change in the user interface from the CommandBars of Office versions 2003 and earlier. However, the VBA programmer will be pleased to learn that using .NET or Visual Studio Tools for Office (VSTO) is not a requirement to control the Ribbon programmatically. In fact, although Ribbon customization requires the use of XML, which definitely is a big change, Ribbon Extensibility ("RibbonX") is actually easier to manipulate using VBA.
Here we'll show you how by using a VBA example running within Excel 2007. But in order to manipulate the Excel 2007 Ribbon you'll need to have Excel 2007 installed. If you don't then you'll either need to license a copy or Microsoft has a 60-day free trial version for Microsoft Office available here: http://us20.trymicrosoftoffice.com/default.aspx
Many of the articles that show how to manipulate the 2007 Ribbon programmatically from a COM or .NET add-in are fairly complicated. Not quite insane, but almost. On the other hand, "manual" methods describing how to add XML directly to a workbook or XLA add-in seem complex as well. However, there is a secret weapon to make the whole thing really easy, and it's the CustomUIEditor.exe tool which can be found here: http://openxmldeveloper.org/articles...mUIeditor.aspx
You can simply "jump right in" and use that editor, but some information on how the new Excel 2007 file formats are organized can help. So a little background:
(i) First of all, the new Excel 2007 file formats are actually zipped XML. The new workbook file extensions include ".xlsm", ".xlsx", ".xlam", etc., instead of the traditional ".xls" or ".xla" files found within Excel versions 2003 and earlier. An .xlsm file is a macro-enabled workbook, an .xlsx is a workbook with no macros attached, and an .xlam file is a macro-enabled add-in, that is an "XLA" with macros.
To see that these files are really zipped XML, all you need to do is to change the extension. For example, change the name of a workbook file from, say, "Book1.xlsm" to "Book1.xlsm.zip" and then you can navigate the folders as you can a standard .zip file. Or uncompress it, edit the XML, and then re-compress. Then change the extension back to ".xlsm" and it will be fully functional again -- provided you got the XML editing right!
(ii) If the workbook has code that changes the Ribbon then it will contain an XML folder called 'customUI' and within that you'll find 'customUI.xml'. If it does not exist yet, then you can add it yourself.
(iii) However, changing the extension to .Zip, uncompressing, editing, saving, re-compressing and changing the extension back is a bit of a hassle, so instead you can cut to the chase with the CustomUIEditor.exe
This tool is the key to everything.
With the CustomUIEditor.exe, the steps to making a VBA add-in that alters the Ribbon is exceedingly simple. The following step-by-step will show you how to embed Ribbon XML and VBA code into your workbook or XLA add-in:
(1) Open Excel 12.0 and create a new Workbook .
(2) Add the following code to a VBA module:
Sub myButton_ClickHandler(control As IRibbonControl)
MsgBox "MyButton was clicked!"
(3) Choose File > SaveAs and save the workbook as an .xlam file, that is, as a macro-enabled add-in. You could also save it as an .xlsm file, which is a macro-enabled workbook, but for most purposes the Ribbon should be altered from an .xlam add-in. (More on .xlsm workbook files in the "Addendum," below.)
Note that the default directory for this newly-saved .xlam add-in is the "..\Application Data\Microsoft\AddIns\" directory, which is not necessarily a trusted location and so may not run. You can change which folders are "Trusted" within Excel 2007 via File > Excel Options... > Trust Center. Another alternative is to save your .xlam add-in in the Excel Startup or User Startup ("AltStartup") directory, which are automatically trusted locations by default.
(4) After you have saved the file, you will want to close the file so that you can edit the XML within it. If you saved the file as an .xlam add-in, then you really saved a copy of the workbook and so the .xlam file is actually already "closed". If you created a .xlsm workbook, then you should save and close the file at this point.
(5) Now open the CustomUIEditor.exe
tool, choose File > Open... and then navigate to your newly-created file to open it within the CustomUIEditor. Once you do this, you will still see a blank screen. This is ok, there is simply no CustomUI XML there yet. Now copy and paste in the following:
<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
<tab id="myTab" label="My Tab">
<group id="group1" label="Group 1">
<button id="myButton" label="My Button"
(By the way, if you want a kick... At this point, within the CustomUIEditor.exe, find the "Generate Callbacks" button, which is the toolbar button on the furthest right (with horizontal lines as the icon). Click it. The result is the VBA code stubs required for this Ribbon. Very cool.)
(5) Within the CustomUIEditor, save your file.
(6) Now you can open up Excel 2007 and if your workbook or add-in was placed in Excel Startup or the AltStartup directory then it will load automatically. If not, then double click your file to open it. The changes you made should be seen within a new custom tab titled "My Tab". If you click on the happy face button you will get a message box. (See the attached image.)
The above is basically the minimum required to get an Add-in up and running. I've attached an example which is similar to this, but further includes:
(a) The <customUI onLoad="ribbonLoaded"> tag, which calls the Sub RibbonLoaded() at startup passing in an object implementing IRibbonUI that you can use later to either invalidate a control (causing it to update) or invalidate all controls.
(b )Explicitly calls <ribbon startFromScratch="False">. The default is False, so this is unnecessary, but if you ever wanted to hide ALL the controls, then this is the way to go. Well, almost… a few of the File commands such as File > Open... and File > Save... will remain and would have to be hidden separately if you wanted to do so.
(7) After playing with the add-in I've attached, then re-open the CustomUIEditor.exe
tool and choose the 'Sample' menu too see a few other nice examples.
(8) Then go to Jensen Harris's Office User Interface blog post titled Ribbon Extensibility: A VBA Sample
, where guest writer Savraj Dhanjal provides a very detailed example showing pretty much everything you can do with the Ribbon. (Download his example from the blog post.) You can open up the RxDemo.xlsm workbook within Excel 2007 in order to look at the VBA, and then you can open it within the CustomUIEditor.exe to see the XML. (If you want some fun, click on the "Generate Callbacks" button from within the CustomUIEditor.exe and you'll see an extensive amount of VBA stubs generated automatically for you.)
Well, that's about it. At this point you should be up and running...
Addendum #1: As stated earlier, you can do all of the above using a .xlsm file, instead of using an .xlam add-in, and the process for creating your file would be the same. However, the result would differ in that for an .xlsm file, the ribbon customizations will appear only when the .xlsm workbook is actually active. The customizations automatically appear/disappear when the workbook is activated/deactivated or opened/closed. (You have no control over this.) This is very nice for context-specific functionality, and avoids the "CommandBar residue" issue that was associated with legacy workbook-attached CommandBars. However, if you want your tools and Ribbon customizations to be available during the entirety of the Excel session, regardless of which workbook is active, then an .xlam add-in is the way to go.
Addendum #2: Within the CustomUIEditor.exe you should make use of the 'Validate' tool button. This is fairly critical as it will tell you if your XML is well formed and provide a (sometimes-understandable) message about what is wrong. Otherwise, when you run it, if there is the slightest mistake the XML will simply not load anything without throwing an error, and there would be no way to know what exactly is wrong.
Addendum #3: With the 'RibbonX Addin.xlam.zip' attachment included below, you do not actually need to "unzip" it. After downloading the file, just knock off the '.Zip' extension because XML Files are already compressed!