Modifying the Ribbon – Part 1 of many

I've decided to start playing around with the Ribbon, and figured that I might as well document my journey through this new piece of Excel. I'm going to start fairly slow with it, and add more as I experiment with different pieces.

For reference sake, the examples in this and following posts will be constructed running Office 2007 on Windows Vista, both of which are the RTM, not Beta versions. You will need Office 2007 to follow along, but you don't need to be running it on Vista, per se. My understanding is that it will help to be using the full version, but if all you have is access to the Beta2 or Beta2TR versions, then you should be okay for the most part.

It would also be a great help to have the CustomUI tool that you can download from OpenXML Developer. If you don't want to do things the easy way, you can always follow the hard route from Microsoft's article. 😉

Okay, so here we go...

Part 1 - The basic XML framework

The ribbon is constructed in XML, and we'll need a little XML to make our own. First though, we'll go into Excel and create a new workbook (or use the default blank one). Save it as an "Excel Macro Enabled Workbook" (xlsm), and close the file.

Now, open the Office 2007 CustomUI Editor that you downloaded above, and open up your Excel file. You'll notice that you are staring at a blank page. Paste the following code in there:

[xml]



[/xml]

Now... this won't do anything for you, but basically it's the main framework that we'll adjust when we're playing with it. See the blank space in the middle? That's where we're going to put in the XML that we actually want to use to do stuff. They key is that any (and as many) tabs we want to create will go between the "tabs" and "/tabs" tags.

Part 2 - Add Some Useful XML
Now, we're going to drop in a little more XML to round things out. In that blank space, we'll add some code to create a new tab on the ribbon called "Custom Tab". Because it's boring to have a totally empty tab, we'll add a group to it, called "Custom Group". No point in leaving that empty either, so we'll put in 3 buttons. We'll call them something really imaginative, like "Custom Button 1"... you can probably guess the rest.

So here's the XML for that. Just drop it in the blank space above:

[xml]

15 thoughts on “Modifying the Ribbon – Part 1 of many

  1. Hi Ken,

    You can setup Excel 2007 so that it shows you what is wrong with your Ribbon customisation code:

    Click the Office button and select "Excel Options".

    Click the Advanced tab

    Find the "General" section and check the box next to "Show Add-in user interface errors".

  2. Hello,

    thanks for this interesting code. However, I have difficulties while using it in XLMA (add-ins) code : "Unabe to find the function CallAction in this workbook. Either the function doesn't exist, or the add-ins are disabled".
    Any idea ?

  3. Hi Aurelien,

    Where did you put the code? It should be in a standard module, not a worksheet, class or ThisWorkbook module. I was able to copy/paste the first CallControl routine and the XML to a workbook, save it as an XLAM file, and it worked just fine.

  4. Thanks for this very useful lesson, quite easier than MS's way!! I've just got a question : how do you find the list of all the imageMso? In your exemple, it's always the HappyFace. Is it possible to create some more pictures? Thanks for your answers, regards, Olivier

  5. Hi Olivier,

    I'm glad you found it useful. 🙂

    With regards to finding which Mso images to use, you can check out the following links.

    The hard way, but requires no additional software downloads: https://excelguru.ca/blog/2006/11/26/modifying-the-ribbon-part-3/

    A bit easier, but requiring a download from Microsoft, is to get their Excel 2007 icon gallery. You can download that here: http://www.microsoft.com/downloads/details.aspx?familyid=12B99325-93E8-4ED4-8385-74D0F7661318&displaylang=en

    And if you want to make that a little easier to use, read my blog post on it here: https://www.excelguru.ca/blog/2007/02/22/office-2007-icon-gallery/

    Hope this helps,

    Ken

  6. Thanx for this. Another tip is that if you put the file in the directory C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns
    Then open Excel, click on the Office logo
    Click on Excel Options
    Click on Add-ins
    Click on Go
    The file name should be there again. If you check it, the ribbon will appear on any file

  7. Hi Peter,

    Interesting concept, that. Honestly, though, if I'm going to the trouble of saving the file in the addins folder, I'd rather save it as an addin file type as well.

  8. Hi Ken,

    One thing I'm struggling with. I have a status label in a custom group on the ribbon with^getLabel callback defined. Elsewhere in the code I have a slow macro. I would like to be able to invalidate the label control twice, first at the start of the macro to get "Processing" and once at the end of the macro to get "Done". However it looks like both fire on macro exit so I never see the "Processing" bit. Am I missing something really basic here?

    Thanks for your help

  9. Hi David,

    Are you trying to put both label status messages from the getLabel callback? I would think you'd probably want to set things up like this:

    Dim State as long '< --Module level variable Sub Button_Click Set State = 1 Invalidate the Label -->Run Slow Macro

    Set State = 2
    Invalidate the Label
    End Button_Click

    I'd then set the label's text based on the State variable.

    Now, I haven't tried this myself, so it may not work, but that's the route I'd start out with.

    Hope this helps,

    Ken

  10. Hi Ken,

    Thanks for the rapid reply. The above is what I have tried. When the Button_Click action terminates the label gets updated by 2, but I never see the 1. I've played around with some other ideas but no joy so far.

    David

  11. This seems to work (I tried it with a zero delay in OnTime) but that gave the same results)

    Private nState As Long

    Public mgrxIRibbonUI As IRibbonUI

    Public Sub rxLoadCustom1(ribbon As IRibbonUI)

    Set mgrxIRibbonUI = ribbon
    End Sub

    Public Sub Define(control As IRibbonControl, ByRef returnedVal)

    Select Case nState

    Case 1: returnedVal = "Starting Macro"

    Case 2: returnedVal = "Macro Finished"
    End Select
    End Sub

    Public Sub MyMacro()

    Call StartLabel
    Application.OnTime Now + TimeSerial(0, 0, 1), "DoStuff"
    End Sub

    Private Sub StartLabel()
    nState = 1
    mgrxIRibbonUI.Invalidate
    End Sub

    Private Sub DoStuff()
    Dim i As Long
    Dim j As Long

    For i = 1 To 10000

    For j = 1 To 60000

    Next j
    Next i

    nState = 2
    mgrxIRibbonUI.Invalidate
    End Sub

  12. Glad you understood that, on re-reading I see I could have given a few more details 🙂

    BTW, this is a somewhat subtle way of telling the user something is happening. I am much more of an in-your-face type of developer (ask Ken :)), so wouldn't a progress bar be better?

  13. I'll have a think about the progress bar idea. I must admit being more an algorithm guy than anything else, so I'm only just upgrading from buttons in worksheets ...

    The origin of the label is that the slow macro computes a portfolio valuation and cab fail for a number of reasons. Once the macro exits the label indicates the number of errors and warnings, with a drop down just below giving the list of what went wrong. In those conditions having the label change to Waiting when you pushed the Compute button seemed natural so that the user wasn't left looking at an old message. I gray out the active sheet while the calculation is going on so that the user isn't tempted to read the output before it's there.

    Thanks again, your and Ken's help was much appreciated !

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *