Ribbon, You’re Nothing to Me Now

Posted on by Dick Kusleika

Ribbon, you’re nothing to me now. You’re not a brother, you’re not a friend. I don’t want to know you or what you do. I don’t want to see you at the hotels, I don’t want you near my house. When you see our mother, I want to know a day in advance, so I won’t be there. You understand?

Last week I said: “As a developer (not a user), my first impression of the Ribbon is a good one. I fully expect it to turn sour …”

Consider it soured.

Whilst struggling with the Ribbon over the last week, I took stock of what is important in a menu. There are three basic things that I want to do with a toolbar/menu.

  1. Run code when a button is pressed
  2. Add/delete buttons from existing toolbars
  3. Disable buttons when they’re out of context

The Ribbon runs code when I press buttons. Awesome. There’s probably a way to add buttons to existing groups, but I can’t figure it out. In my add-in, I have this xml code:

<customui xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
    <ribbon>
        <tabs>
            <tab id=“dkInvTab” label=“Invoices”>
                <group id=“dkInvGroup” label=“Invoices”>
                    <button id=“dkInvNew”
                        label=“Create New Invoice”
                        size=“large”
                        onAction=“CreateNewInvoice” />
                    <button id=“dkInvOpen”
                        label=“Open Existing Invoice”
                        size=“large”
                        onAction=“OpenInvoice” />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

It adds a tab, then a group, then two buttons that I always want active. I want to add two more buttons when my invoice template is active. That should be easy enough.

<customui xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
    <ribbon>
        <tabs>
            <tab id=“dkInvTab” >
                <group id=“dkInvGroup” >
                    <button id=“dkinvSave”
                        label=“Save Invoice”
                        size=“large”
                        onAction=“SaveInvoice”  />
                    <button id=“dkInvCalcTax”
                        label=“Calculate Sales Tax”
                        size=“large”
                        onAction=“CalcTax”  />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Nope, that doesn’t do it. I open my template and the Ribbon doesn’t change. The xml is well formed, per the Custom UI Editor. There may be a syntax error in there that’s causing the problem, but I don’t see it. And Excel isn’t going to tell me where it is.

But I really don’t want to add those buttons when the invoice template is active. I want to add them as disabled when the add-in is loaded and enable them when the invoice template is active. I should be able to do that with xml that looks like this:

<customui xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
    <ribbon>
        <tabs>
            <tab id=“dkInvTab” >
                <group id=“dkInvGroup” >
                    <button id=“dkinvSave” enabled=“true” />
                    <button id=“dkInvCalcTax” enabled=“true” />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Nope, that doesn’t do it. Apparently I have to use a getEnabled event to change the enabled status. As far as I can tell, that event only runs when the add-in is loaded. WTF good is that? How exactly is that an event? I’m really struggling to figure out what the people who designed this customization scheme were thinking.

Let’s assume for a moment that there is a way to enable/disable buttons based on the active workbook. Do I have to use event procedures to somehow refresh the ribbon and check what’s active? I already have to do that with the Commandbars object model, so what’s the benefit to me, as a developer, to using the Ribbon? None, that I can see.

So I’m sticking with Commandbars for now. It adds them to the Add-ins tab, which I don’t like. But it allows me to easily enable and disable buttons, which I do like.

I’m a reasonable guy. I’d like to think that the Ribbon is a work in process and that the next iteration will be better. But I’ve culled my list of requirements to three things and they didn’t get those right. How can I expect them to produce something worthwhile when they can’t even get the basics right. I’m soured. Even when someone shows me how to do what I want, I’ll still be soured. It’s not intuitive and it’s hard to debug. I simply don’t like it.

Posted in Uncategorized

25 thoughts on “Ribbon, You’re Nothing to Me Now”

  1. There are a few things you want to keep in mind.

    1. You cannot add or remove controls from a built-in group, though I suppose you might be able to change their visibility.

    2. You cannot add controls to a ribbon tab on the fly. The only opportunity you have is when a workbook is loaded. So add all the controls at once, and selectively adjust their visibility.

    a. You could use RibbonX in a template or workbook that will change the ribbon when the file opens and closes. I’ve used this methodology, and it’s not too bad, but it’s cumbersome to maintain over multiple Excel versions.

    b. You can invalidate controls based on Excel or Ribbon events, then change properties of them (label, enabled state, visibility, icon, etc.) all through VBA. I’ve set up table-driven versions of this which work as well as any menu-toolbar system I’ve built before, and in fact, the same table can drive the ribbon and the menu-toolbar interface when the project runs in 2003.

    FWIW, I like working with RibbonX and VBA much better than working in the Excel 2007 environment itself.

  2. You may be reasonable, but MS Office team is not. Completely changing the GUI in XL/Office should be considered “Crime Against Developers”. Not sure there is an international court for that one, but it may be a good time to start it.
    There is only one way to fix this, revert to original GUI and file the ribbon in the same garbage basket with Vista.

  3. I can live with the change in interface, but they should have extended the same functionality to the new interface. In older versions you could create your own toolbar without touching code, and you could modify all the properties via VBA. Now you have to tweak an XML file in a totally separate application. Any changes and you have to close and reopen the file, so it’s very difficult to play around and see your changes as you go. They should really come up with a menu builder in the editor similar to what you have with the full VB IDE. I wonder if there are any 3rd party tools that do just that.

    As for modifying existing menu items, I recall an earlier discussion about using the startfromscratch option and rebuilding the entire ribbon so that it would be yours to control. Seems like a real PITA, but you’d only have to do it once. Or someone who has already done it could post the file at the Excel User Group site…

  7. Dick, you’re my older brother, and I love you, but don’t ever side with anyone against the Ribbon again.

  8. Dick, I’ve been working on this since your last post. I took Dennis’s suggestion and am using VSTO from Visual Basic.Net 2008. Very fun. There are a lot of things that can be done without using XML, just the VB code. With the push of a button, VSTO creates COM Addins, and with the push of another button, you can create a Setup.exe file to install the addin on other machines. That part is, frankly, beautiful.

    To do the enabling/disabling of custom controls on the fly using InvalidateControl – and for other RibbonXinfo – I found this very helpful: http://www.oaltd.co.uk/DLCount/DLCount.asp?file=Excel2007VBAProgRef_ch14.pdf. (Where would we be without Stepen Bullen?) The one thing I found different in VSTO than in this chapter is that the GetEnabled, etc., routines must be functions, not subs (hopefully somebody can benefit from my hair-tearing on that one).

    Aside from the things Jon mentioned, I also don’t think you can enable/disable built-in controls after the Ribbon is loaded, only at startup. I’ve never done that anyways, my stuff is mostly added menus, with controls that are enabled/disabled based on events. This type of stuff can be completely controlled from VB.Net without XML. Unfortunately, hooking Ribbon controls does seem to require XML.

    One interesting thing is the “Commandbars.ExecuteMso” syntax that’s been added to the commandbars object model in Excel 2007, e.g., Commandbars.ExecuteMso(“FileSave”).

    I think commandbars in XL 2000-2003 are easier to work with, but after few long nights and some frustration, I’m thinking 2007 is pretty cool. I really like being able to work in VB.Net. The IDE is so good.

    I’ve also started messing with programming Task Panes from VB. I like that as well because the .Net controls, like SplitContainer, are very nice.

    Good luck!

  9. Dough…

    This is the tragedy..To do something so simple we need
    a) VB.net, b) VSTO c) XML Editor.
    I think the ribbon is MS’s way of forcing the above technologies on its users

    All this was just Right Click + Customize

  10. Has anybody had any luck with the Quick Access Toolbar? Can it be table driven?

    In J-Walks Excel 2007 VBA book, he has a really nice looking example of a QAT file table, but not muck else, anybody have a link?

    I quote,

    “It’s possible to share a QAT with other users. For example, you may have customized your QAT with two dozen useful tools. If a colleague is impressed, just give him a copy of your Excel.qat file and tell him where”

  12. Sam, I know what you are saying. Certainly for easy, on-the-spot customizing, 2007 sucks. And all that graphic, space-wasting clutter is annoying.

    On the other hand, if the comparison is between ClassicVB/VBA and VB.Net/VSTO it’s not so clear to me. In .Net everything is available to write and deploy a COM addin. The VSTO and XML are part of the programming environment. Debugging and compiling COM is much cleaner than with Classic VB. If you need to use XML, it has Intellisense.

    So although it’s made simple things harder, I think it’s also making hard things simpler.

  13. “…in the same garbage basket with Vista.”

    … and Bob, and Clippy, and Hailstorm …

    (Who am I to talk? I’ve had as many failures as MS, without the publicity and without anywhere near the financial success as Windows and Office.)

  14. I don’t disagree with tool bars, but they must be configurable in both the Layout and Context
    I want my regurly used tools “Format Painter” and “Paste Values” right in the middle of the screen, at the top, and always accessible, not a “Home” click away from where I am working

  15. Sam:

    The only conclusion we can make is that MSFT no longer make any new investments in VBA but in .NET/VSTO, i.e. all the nice tools etc will be available on the later platform. MSFT will support VBA to version 14 but they didn’t say that they will make any huge investments to it, or did they?

    The Ribbon UI is well suited for its main purpose:
    To support (top) managers when they point and click to view corporate’s Excel reports published through SharePoint servers.

    No matter what MSFT does, power users and native developers of Excel are not part of the targeting group. Instead, start to think in terms of large corporates.

    “Google” for Office Business Applications, or just OBA, and You will find out more.

    Kind regards,
    Dennis

  16. When I read this, I instantly thought to just look at the getenabled event, and derive your own custom event from it, with changes so it behaves the way you want.

    OOP is beautiful, you just have to understand the pillars man. Prob solved.

  17. i think someone’s hacked your RSS feed, unless of course you’ve started advertising music, adobe, and viagra as a side project!

  20. HI(sorry for the caps, I didn’t realise it was on!!)
    I HAVE BEEN ASKED TO BUILD A ‘CLINICAL GOVERNANCE DASHBOARD’ AT WORK, THIS WOULD SHOW ABOUT 15 DIFFERENT INDICATORS OF THE STATE OF THE HOSPITAL THAT I WORK IN. I CAN DO ALL THE CODE FOR IT, HOWEVER I WOULD LIKE TO ADD A RIBBON SO THAT ITS ACCESSIBLE WITHIN EXCEL, NO MATTER THE USER. HAS ANYONE GOT A REALLY EASY example and walk through on how to do this?
    If I can get a ribbon to appear with a button on it, then I would be more than able to add more buttons etc and this would make the whole project more accessible and neater to users than having buttons on the sheet.
    I have done small amounts of C# but work mainly with VBA.

    Any help, or suggestions would be much appreciated!!!

    Disillusioned NHS employee!!

  22. Hi Ron,

    This is sooooooo useful!!!! THANK YOU!!!

    One other question, is there a way to have a drop-down menu on the ribbons?

    What I’d ideally like to be able to have a drop down menu that would list all the wards/departments and be populated from the workbook, would you have to do this from within the workbook using vba, or would you have to define the list in the xml?

    Thanks again

    Mark

  24. Hi sharkboy

    I create three example workbooks but not have them online on this moment
    Must check a few things before I upload them.
    If you want them to play with it this weekend mail me private
    and i send them to you.

  25. Well I guess I’m *really* late to the party. Here it is 2011 and I’m getting into Office 2007 for the first time. (My organization is slowly upgrading finally.)

    I’d like to say: I feel the pain of those that lament that what used to be an easy editing job is now ridiculously complicated.

    Having said that, I actually think MS actually *did* think all this through before they did it. Was it to force new (Microsoft) technologies on people? Well, IMHO not exactly, at least not primarily.

    It comes down to this: The old button bar system might have been easy to edit, but it wasn’t very actually useful for the Standard Generic Office User.
    * Buttons would fall off when you didn’t use them, but then you’d need them again and you’d have to know how to get them back.
    * Almost every command available had a button no matter even if it was one that nobody ever used ever

    The ribbon sacrifices ease-of-customization for ease-of-use for the (novice) Standard Generic Office User. The ribbon is a real pain for anyone wanting to customize it, but it will probably be a major benefit to any novice office user.

    That’s why MS changed it, and that’s why MS will be deaf to any cries of complaint from developers. So it goes.

    P.S.
    When I first looked at the thing, I didn’t think it would be customizable *at all*. At first glance, it looks as if it’s just one big graphic driven by a mouseOver position map underneath. After reading some, I realize that might not be entirely accurate, but I think the comparison still gives one a good sense of how customizable it isn’t.

    –
    Furry cows moo and decompress.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.