Ribbon Extensibility: A VBA Sample

Today's Guest Writer: Savraj Dhanjal

Savraj is a Program Manager on the Office User Experience team focused on user interface extensibility for Office developers.

Now that you've got Beta 2, you can download and open RxDemo.xlsm, a new file format document that uses RibbonX and works on Beta 2.

If you haven't changed your Macro security settings from the default, you'll receive a security alert. If you trust the document, click "Enable Content..." After carefully reading the ensuing dialog box, select "Enable this content." If everything worked, you can now click around on the custom controls. Notice the items added to the Office Menu, the custom "RibbonX" tab that can be made visible on from the add-ins tab, and the various controls that you should recognize from previous blog posts.

Take a look at the code behind the buttons in VBA, and if you want to look at the markup behind the document, I suggest you try out the Custom UI editing tool, a tool that lets you insert and edit images and the customUI part in new file format documents. There are some comments in the code as well as a number of explanatory super tooltips in this sample, so take a look and enjoy!

Comments

• Anonymous
  June 01, 2006
  I just wanted to let everyone know that I am blogging on RibbonX and other UI customization issues at http://pschmid.net

• Anonymous
  June 01, 2006
  Can I ask, what's the point of zipping the file considering that the xml documents are a form of zip format.  You'll see what I mean if you change the extension of an office XML document to zip

• Anonymous
  June 01, 2006
  I zipped it because the web server it's hosted on won't serve .xlsm files.  Probably something to do with the MIME types, but I don't administer the server and therefore I can't fix it.
  
  In other words, it's a zip file because otherwise you wouldn't be able to download it.

• Anonymous
  June 01, 2006
  Found a bug in the sample - cb1_ItemLabels() and dd1_onAction() try to add an integer to a string and cause an exception; probably ought to use a "&" instead of "+" :)
  
  Aside from that this is a really helpful example - thanks very much for posting it!
  

• Anonymous
  June 01, 2006
  Hi Chris, thanks for catching that. ;)

• Anonymous
  June 01, 2006
  Hi Savraj, thank you very much for doing a vba example! I shortly looked into it late in the evening yesterday and I'm sure it'll give answers to some questions (i.e. formatting text in Supertips, maybe even how to dynamically extend the ribbon since there's some sample code for dynamically creating menu entries in it)
  
  So now I'm looking forward to learn more from the example at home (at work I don't have a PC with O2k7 yet and I don't want to co-install O2K7 with O2K at work because at home I discovered some issues in coexistence with my OEM Word XP: Sending faxes opened the Outlook address book instead of the WAB and I can't make it open WAB again, Photoeditor can't open PNGs anymore)
  
  PS:
  I really hope the QAT can be extended by developers in the final version as well (i.e. to be able to place a "Print current page only" button next to the "Print" button for all our corporate users.)
  

• Anonymous
  June 01, 2006
  The VBA support for RibbonX seems to rely on the custom UI being included as an xml part in the file, saved with the new file formats.
  
  Is there any way to use VBA with RibbonX:
  - When saving in xls format, so the same file can be used in previous versions (with the Ribbon bit ignored and without requiring distribution of the xlsm converters)?
  - To provide the custom UI at runtime (e.g. through an initial call into the ThisWorkbook class) rather than at design-time (in the file)?
  

• Anonymous
  June 02, 2006
  May I ask how to see VBA code or open VBA editor as we do tools -> macro -> Visual Basic editor in excel 2003? I fail to find any VBA thing in the file, thanks.
  

• Anonymous
  June 02, 2006
  Dino - I found the answer to this question by using the transition guide at http://officebeta.iponet.net/en-us/help/HA101491511033.aspx
  

• Anonymous
  June 02, 2006
  Hi, I love Office 2007 but I wish to make one suggestion.  Is there anyway possible that for Outlook, you could override HTML e-mail fonts with whatever the user would like to select?  Say for instance you receive an e-mail from someone like a newsletter.  It comes with random fonts and font sizes.  I'd like to be able to override all of those fonts and font sizes by using say Verdana, size 9.  Is there anyway that this would be possible or is it too late to implement something like this?  It would be similar in nature to how IE overrides web pages font styles and sizes.  Also how IE lets you use a style sheet to override HTML styles.

• Anonymous
  June 03, 2006
  Stefan,
  
  RibbonX has a section for QAT. You should be able to put your QAT customizations into there. If you have trouble with that, post a comment to my blog at http://pschmid.net, as I don't check this comment page normally.

• Anonymous
  June 03, 2006
  The comment has been removed

• Anonymous
  June 03, 2006
  @Patrick
  Thank you, I didn't have a look into that already. But if it's not a supported way to do that like Jensen said, I doubt I'll imoplement it that way.
  
  @Jensen
  Problem is we currently have a "Print current page"-Button next to the Print button in Word 2000. Our users click that button quite often i.e. to print quickly just the one page they changed.
  
  In the new UI the print-button is only in the QAT, not in the ribbon. But the "print-current-page"-button has to be next to the print-button. Everything else would make it intransparent to the user.
  
  To put the button in the menu or the Ribbon and let the user add it on his own is no good option if you have users with very different skills (and of course sometimes new users), you want to keep support costs low and have a consistent user interface around the whole company.
  
  Of course this add-in is not commercial but company-specific. I think there should be at least the option for companies to define the initial QAT on roll-out time. Maybe for that purpose you could provide an internal "print current page" button?
  

• Anonymous
  June 05, 2006
  I have tried to finds out where you started the ribbonX tool but cann't find it please tell me hw you did this

• Anonymous
  June 05, 2006
  I've been playing around with Excel 2007 workbook and worksheet protection.  I find it interesting that when you protect the workbook, it actually encrypts the zip container (disabling the extract option).  Could you explain a little about what goes on in the background when a workbook is protected?

• Anonymous
  June 05, 2006
  Hi Jensen,
  
  I inferred from Stefan's question that he was looking to customize Office for a particular company (as he later confirmed). There is absolutely nothing wrong for a company to push a particular Office UI with ribbon and QAT customizations to all their users. Companies have been doing this for a very long time with Office and if you took away their ability to define a basic QAT, you would have some very unhappy customers.
  For add-ins in general though, I totally agree with you. Unless the add-in is particular to one specific company (and the company requested it in the QAT), add-ins should stay clear off the QAT. As you said, this is not the place to advertise an add-in.

• Anonymous
  June 05, 2006
  I concur with Stephen Bullen: It would be VERY welcome if the initial call to ThisWorkbook (or ThisDocument) simply checks if it implements IRibbonExtensibility.
  
  Then at least a VBA developer can create 1 solution for Office 12 and prior versions.
  
  Dont get paranoid about what "commercial" developers might do to "your" ribbon. They'll have enough sense to leave the QAT alone.

• Anonymous
  June 06, 2006
  No, a check for ThisWorkbook implementing IRibbonExtensibility won't work, as it will cause a compile error in prior versions. But I don't see any reason why a new ThisWorkbook_GetCustomUI event wasn't added (which simply wouldn't get called in prior versions).

• Anonymous
  June 06, 2006
  The comment has been removed

• Anonymous
  June 06, 2006
  Oh yeah, about not be able to screen grab O2K7 button images, my point was, could you make them as PNGs available to download so we could use them? Thanks

• Anonymous
  June 07, 2006
  Hi Savraj, I've another query/request. In the Excel example, you've added buttons to the Print menu and send menu on the office button. Is it possible to actually add buttons to the office menu itself? Like a button between New... and Open...? Or a Menu or split button between Print...> and Finish>? If it's not possible could it be made possible? There's currently nowhere to put buttons relating to Opening as there's no open menu, and ideally I'd like a menu for all file-related buttons for my add-in. Thanks.

• Anonymous
  June 07, 2006
  Hi Joe,
  
  the following few lines of RibbonX should do the trick:
  <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="ribbonLoaded">
  <ribbon>
     <fileMenu>
         <button id="NewButton" insertBeforeMso="Open" label="New Button" />
     </fileMenu>
  </ribbon>
  </customUI>

• Anonymous
  June 07, 2006
  That's excellent Patrick, Thank you very much.

• Anonymous
  June 10, 2006
  A Splitbutton would make more sense if it "adapts" label and action to the "current" or "last selected" item.
  Pressing the button executes the current menu item. Pressing the dropdown shows the menu and selecting a menu item changes the default choice.
  PasteSpecial...  adapts to Values/ Formulas
  
  Similar a togglebutton: It would be nice if the label and image could be changed with the state of the button. e.g. Calculation: Auto/Manual.
  
  As far as I can see the GetLabel callback is only run once: when the toolbar is first displayed.
  
  

• Anonymous
  June 10, 2006
  you can trigger GetLabel multiple times by invalidating a control.
  However, keep in mind that in the Office UI labels don't change during runtime and neither should you do this in your add-in. Menu content is the only thing that changes dynamically at runtime.

• Anonymous
  June 10, 2006
  Patrick.
  I'm exploring the functionality of the Ribbon from a developer pov. I found important things I could but can't and some I can but couldn't.
  
  I'd missed the purpose of the .Invalidate method. It may be what the docter ordered. Curious name though.
  
  "Neither should you" has got me worried.  
  I'm still finding out if I can if I want to.
  
  

• Anonymous
  June 10, 2006
  Take a look at my blog: http://pschmid.net
  It has a lengthy discussion of what you should do to follow the Office UI guidelines. I also discuss all the things you can do, even those not mentioned in the Microsoft documentation ;)
  Invalidate simply tells Office that a particular control, tab or group is no longer valid in the UI meaning that it should call all "get" callbacks of it again to get its new status.

• Anonymous
  June 10, 2006
  Invalidate was the trick!
  
  However: i think i've got a bug:
  GetLabel of a Button in SplitButton container. gets called onLoad but doesn't get called on Invalidate. GetImageMso does.
  
  For clarity: I'm figuring out how to make a multilanguage ribbon. User makes language selection and ribbon must relabel to chosen language.
  
  
  

• Anonymous
  June 14, 2006
  Designflaw or bug?
  
  I found that for VBA the callbacks in the XML must be fully qualified. e.g. getEnabled="testbook.xlsm!cbGetEnabled"
  
  Otherwise when switching documents/worksbooks
  (with the custom tab visible) several errors are generated like:
  "Cannot run the macro "cbGetEnabled". The macro may not be available in this workbook or all macros may be disabled."
  
  Is there a simple means to specify my container name via a constant or function in XML or do I need to: unzip, search & replace,rezip. when I simply rename my workbook?
  

• Anonymous
  June 17, 2006
  keepITcool: bug. Thanks for your email. I bugged it
  Btw, you should try the custom UI editor tool Savraj posted a link to in the post above. It makes one's life of editing RibbonX with an Office document/template a lot easier.

• Anonymous
  July 24, 2006
  Based on reading the posts, I don't think this example helps me at all.  I develop an XLT that is distributed to about 300,000 users, and I don't know what version of Excel they have. They don't know either, probably.
  
  I need one XLT file that will work with all the versions of Excel and that will add a UI as applicable, by excel version. How does one modify the ribbon using just VBA as we could modify the menus and toolbars before?
  

• Anonymous
  July 24, 2006
  Tim: Unfortunately, you can't. Excel developers have reverted to using a combination of an Excel 97-2003 file with an Excel 2007 add-in for the RibbonX to make one add-in for all Excel versions.

• Anonymous
  March 20, 2007
  PingBack from http://proofficedev.com/blog/2007/03/20/programmingcustomizing-the-excel-2007-ribbon-using-vba/

• Anonymous
  October 27, 2008
  PingBack from http://mstechnews.info/2008/10/the-office-2007-ui-bible/

• Anonymous
  June 12, 2009
  PingBack from http://insomniacuresite.info/story.php?id=194