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 (35)

  1. I just wanted to let everyone know that I am blogging on RibbonX and other UI customization issues at http://pschmid.net

  2. Alex says:

    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

  3. jensenh says:

    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.

  4. Chris Smith says:

    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!

  5. Savraj says:

    Hi Chris, thanks for catching that. 😉

  6. Stefan KZVB says:

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

  7. 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)?

  8. Dino Hsu says:

    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.

  9. Kevin Campbell says:

    Dino – I found the answer to this question by using the transition guide at http://officebeta.iponet.net/en-us/help/HA101491511033.aspx

  10. Nocturnal says:

    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.

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

  12. jensenh says:

    Let me follow-up to Patrick’s comments by saying:

    Please do not have your commercial add-in add things to the Quick Access Toolbar.

    The Quick Access Toolbar is reserved for customization by the user.  You should not be spamming icons into the QAT: it is for the user to decide what icons they would like to take up that precious space.

    The Quick Access Toolbar on-disk persistence format is not documented and is not guaranteed to work from version to version.  If we find that commercial add-ins start hacking this file to put icons in, we will likely do work to make this harder/impossible.

    The right model here is: You add your UI to one of the supported entry points (Ribbon, the Add-Ins tab, the Office Menu) and then the user decides if they would like ta add your feature to the QAT based on its importance to them.

    It is not a place to advertise your features.

  13. Stefan KZVB says:

    @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?

  14. stabij says:

    I have tried to finds out where you started the ribbonX tool but cann’t find it please tell me hw you did this

  15. Mike Alexander says:

    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?

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

  17. keepITcool says:

    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.

  18. 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).

  19. Joe says:

    Thanks for the examples and the Custom UI Editor – it’s a little simple (wouldn’t mind a recently opened file list, a find feature, and undo and redo – is this program finished or in development?) but it’s still extremely useful.

    I’ve notice that semi-transparent bits of my icons are coming through semi-transparent, but black (if you see what i mean). I had a blue arrow, that in paint.net faded to invisibility along its tail, but when i added the PNG to the ribbon, the faded bit was a block of black that faded, not blue. Anyone else noticed this? It makes icons that look perfectly good in any drawing program look very ugly in word 2K7.

    Another thing, is that wherever possible, I like to use components of existing word icons in my own icons, so their meanings are familiar and intuitive. For example i have an "Open XML" button, and in word 2K3 i just did a screen grab of the open button, and added "XML" above the image. My point is, screen grabbing the Word 2k7 buttons isn’t enough because i don’t get the transparent shadows from existing icons.

    I’d also like to reiterate my request to be able to modify existing groups. It totally contradicts your suggestion that we put buttons where users will look for them and creating a new group for a single button on the home page is ugly and a waste of space. Please reply to this one if nothing else!

    Finally, on a more trivial note, is there any reason why you have decided not to tip your folders on their end the way Vista does for your "Open" icons? Shouldn’t they match?

    Many thanks

  20. Joe says:

    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

  21. Joe says:

    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.

  22. Patrick Schmid says:

    Hi Joe,

    the following few lines of RibbonX should do the trick:

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui&quot; onLoad="ribbonLoaded">

    <ribbon>

       <fileMenu>

           <button id="NewButton" insertBeforeMso="Open" label="New Button" />

       </fileMenu>

    </ribbon>

    </customUI>

  23. Joe says:

    That’s excellent Patrick, Thank you very much.

  24. keepITcool says:

    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.

  25. Patrick Schmid says:

    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.

  26. keepITcool says:

    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.

  27. Patrick Schmid says:

    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.

  28. keepITcool says:

    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.

  29. keepITcool says:

    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?

  30. Patrick Schmid says:

    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.

  31. Tim Teichman says:

    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?

  32. Patrick Schmid says:

    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.