Modifying the Ribbon – Part 8

Posted on December 2nd, 2006 in Excel,Office 2007,The Ribbon by Ken Puls

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this article I'm going to demonstrate a very simple menu created on the Ribbon.  This particular menu will only hold two buttons on it, but we'll get into some larger examples in future posts.

XML Markup

The XML Markup provided below is very slim.  It still adds our XLG tab and a Testing group, but only adds one menu item on that tab.  The rest of the menu items will be added dynamically at runtime.

PLAIN TEXT
XML:
  1. <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
  5. <group id="TestTab" label="My Testing Tab">
  6. <dynamicMenu id="menu1" label="My Menu" getContent="GetContent" />
  7. </group>
  8. </tab>
  9. </tabs>
  10. </ribbon>
  11. </customUI>

VBA Code Required

Unlike the last couple of examples, no code is required in the ThisWorkbook module at this point.  We will need to add our callback code to populate the menu, and also the onAction code to react when a button is clicked.  That code is all shown below:

PLAIN TEXT
Visual Basic:
  1. Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
  2. 'Populate a menu item
  3. Dim sXML As String
  4.  
  5. 'Open the XML string
  6. sXML = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"
  7. 'Add a button
  8. sXML = sXML & "<button id=""button1"" label=""Button 1"" " _
  9. & "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
  10. 'Add another button
  11. sXML = sXML & "<button id=""button2"" label=""Button 2"" " _
  12. & "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
  13. 'Close the menu string
  14. sXML = sXML & "</menu>"
  15. 'Return the completed XML to the RibbonUI
  16. returnedVal = sXML
  17. End Sub
  18.  
  19. Private Sub btnCentral(control As IRibbonControl)
  20. 'Feed back which button was clicked
  21. MsgBox "You clicked " & control.ID
  22. End Sub

The code above will result in a menu that looks like this:

How it works

Clicking "My Menu" will result in the GetContent routine being fired.  This routine is looking to pass the XML back to the RibbonUI so that it can be "merged" into the RibbonX code and populate the child items.  The code requires no line formatting, so we basically just build a string of XML to create a menu.  The code in the GetContent routine must start with the customui line, and be properly formed XML.

To demonstrate this a little clearer, the dynamic XML we created above to populate the buttons, (with formatting added,) is shown below:

PLAIN TEXT
XML:
  1. <menu xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <button id="button1" label="Button 1" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
  3. <button id="button2" label="Button 2" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
  4. </menu>

And, of course, because we specified the OnAction parameter as "btnCentral", it will fire the btnCentral routine when we click either button.  That code is set to tell us what button we clicked.

12 Comments

12 Responses to 'Modifying the Ribbon – Part 8'

Subscribe to comments with RSS or TrackBack to 'Modifying the Ribbon – Part 8'.

  1. Mario said,

    on February 4th, 2007 at 6:48 am

    instead of a "MsgBox" i want to be able to select page in my workbook ,,,,,,help me please :-)

  2. Mario said,

    on February 4th, 2007 at 7:08 am

    and vba code=

    Private Sub CallControl(control As IRibbonControl)
    Select Case control.ID
    Case Is = "Button1"
    Sheets("Page Acceuil").Select
    Case Is = "Button2"
    Sheets("Feuille de temps").Select
    Case Is = "Button3"
    Sheets("Note de frais").Select
    Case Is = "Button4"
    Sheets("Dépenses1").Select
    Case Is = "Button5"
    Sheets("Dépenses2").Select
    Case Is = "Button6"
    Sheets("Dépenses3").Select
    End Select
    End Sub
    Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
    'Populate a menu item
    Dim sXML As String

    'Open the XML string
    sXML = ""
    'Add a button
    sXML = sXML & ""
    'Add another button
    sXML = sXML & ""
    'Close the menu string
    sXML = sXML & ""
    'Return the completed XML to the RibbonUI
    returnedVal = sXML
    End Sub

    Private Sub btnCentral(control As IRibbonControl)
    'Feed back which button was clicked
    Select Case control.ID
    Case Is = "Button1"
    Sheets("Page Acceuil").Select
    Case Is = "Button2"
    Sheets("Feuille de temps").Select
    End Select

    End Sub

  3. Ken Puls said,

    on February 4th, 2007 at 9:38 am

    Hi Mario,

    My blog has issues with displaying XML. Can you email that to me? (You can send me the workbook if you like as well.)

    Email is ken at excelguru dot ca

    Thanks,

    Ken

  4. tom said,

    on April 9th, 2007 at 8:48 am

    I am trying to run the code to create a menu in Microsoft Access but keep getting an error "Microsoft Office can't run the macro or callback function 'GetContent'.

  5. Goran said,

    on May 15th, 2007 at 1:47 am

    A very good guide in dynamic menus. Exactly what I've been looking for!!

    BUT... Is there a way to reset the dynamic menu? The first time you open the dynamic menu, the GetContent-procedure is called. But only once. Next time when I open the menu the procedure is not called since the dynamic menu is already populated.

    I would like to call the GetContent procedure each time i open the menu. Is that possible?

  6. Ray R. Gable, Jr. said,

    on August 23rd, 2007 at 1:10 pm

    An example of is provided at http://www.oaltd.co.uk/Spreads/Excel2007VBAProgRef_ch14.pdf (pg. 309 - Updating Controls at Run Time).

    Essentially, the GetContent procedure can be called each time the menu is clicked by calling the InvalidateControl method of the iRibbonUI interface.

    Based on the example above, there are 3 changes which need to be made to have the menu dynamically populated when clicked:

    Step 1:
    In the Microsoft Office 2007 CustomUI Editor, tell Excel to give you an iRibbonUI interface by adding the onLoad callback to the customUI element to the XML:

    Step 2:
    In the VBA module, create a global variable which will be used to invalidate the menu. The variable will be initialized when the workbook is opened/XML loaded.

    Dim cRibbon as IRibbonUI

    Private Sub sbCustomUI_onLoad(ribbon as IRibbonUI)
    Set cRibbon=Ribbon
    End Sub

    Step 3:
    Add the following line of code at the end of the GetContent procedure to dynamically rebuild the menu each time it's clicked:

    cRibbon.InvalidateControl "menu1"

    Hope this helps,

    Ray R. Gable, Jr.

  7. Robert Paulsen said,

    on April 21st, 2008 at 8:32 am

    Can you reset the IRibbonUI global variable?

    In one of your examples you had the following code:
    Dim cRibbon as IRibbonUI

    Private Sub sbCustomUI_onLoad(ribbon as IRibbonUI)
    Set cRibbon=ribbon
    End Sub

    If cRibbon gets set to Nothing is there a way to reset it?

  8. Ken Puls said,

    on April 21st, 2008 at 9:55 am

    Hi Robert,

    Unfortunately, no. This is the single biggest issue that we have when creating building and debugging ribbon modifications. I'm hoping that we'll see this ability in Office 14, but only time will tell.

    Right now, if you lose your RibbonUI object, the only recourse is to reload the workbook/addin to reset it. It's a one shot deal.

  9. Kevin said,

    on April 25th, 2008 at 10:29 am

    Hi,

    So, I have an add-in in which I updated xml with a customUI. This menu loads when I start up excel, but if I open a specific workbook direct from my documents, the menu doesn't load.

    Any help would be great!

    KML

  10. Ken Puls said,

    on April 28th, 2008 at 7:58 pm

    Hi Kevin,

    Out of curiosity, is the Add-in loaded when you open the document in this way?

  11. sujith said,

    on July 10th, 2008 at 10:43 pm

    I would like to know how to disable/hide each tab in ribbons except the Add-ins tab. My custom toolbar is in Add-ins Tab.This should be done through macro code.
    In excel 2003, we used to do this with the following code

    Dim c as commandbar
    For each c in application.commandbars
    c.enabled=false
    next

    Please help.

  12. Ken Puls said,

    on July 11th, 2008 at 10:31 pm

    Sujith,

    You're correct in that the way you mentioned works in 2003, and also that your toolbar will manifest in the add-ins tab in 2007.

    The bad news... you can't do this using VBA alone. The easiest way to do what you are after is to save the file as an xlam, create XML code to set the startFromScratch attribute to true, and specifically declare tab idMso="TabAddIns" visible="true"

    Any way you slice it, though, you'll need to write some XML.

    Hope that helps,

    Ken

Post a comment