Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Knowledge Base > Tutors' Corner > Customizing the Excel 2007 Ribbon using VBA


Reply
 
Thread Tools Display Modes
  #1  
Old 06-29-2006, 01:44 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default Customizing the Excel 2007 Ribbon using VBA


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 tool.

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:
Code:
Sub myButton_ClickHandler(control As IRibbonControl) MsgBox "MyButton was clicked!" End Sub
(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:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
  <ribbon>
    <tabs>
      <tab id="myTab" label="My Tab">
        <group id="group1" label="Group 1">
          <button id="myButton" label="My Button"
                  imageMso="HappyFace" size="large" 
                  onAction="myButton_ClickHandler" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
(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...

Good luck!
Mike


Addendums:

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!

-- Mike
Attached Images
File Type: jpg My Tab Screen Shot.JPG (62.0 KB, 779 views)
Attached Files
File Type: zip RibbonX VBA Addin.xlam.zip (13.1 KB, 1134 views)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Colin Legg; 02-25-2010 at 09:43 PM. Reason: Fixed 18,000 year oversight.
Reply With Quote
  #2  
Old 07-11-2006, 11:01 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by wayneph; 01-16-2007 at 01:36 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->