Modifying the Ribbon – Part 8
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.
-
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
-
<ribbon startFromScratch="false">
-
<tabs>
-
<tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
-
<group id="TestTab" label="My Testing Tab">
-
<dynamicMenu id="menu1" label="My Menu" getContent="GetContent" />
-
</group>
-
</tab>
-
</tabs>
-
</ribbon>
-
</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:
-
Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
-
'Populate a menu item
-
Dim sXML As String
-
-
'Open the XML string
-
sXML = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"
-
'Add a button
-
sXML = sXML & "<button id=""button1"" label=""Button 1"" " _
-
& "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
-
'Add another button
-
sXML = sXML & "<button id=""button2"" label=""Button 2"" " _
-
& "onAction=""btnCentral"" imageMso=""FileSaveAsExcel97_2003"" />"
-
'Close the menu string
-
sXML = sXML & "</menu>"
-
'Return the completed XML to the RibbonUI
-
returnedVal = sXML
-
End Sub
-
-
Private Sub btnCentral(control As IRibbonControl)
-
'Feed back which button was clicked
-
MsgBox "You clicked " & control.ID
-
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:
-
<menu xmlns="http://schemas.microsoft.com/office/2006/01/customui">
-
<button id="button1" label="Button 1" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
-
<button id="button2" label="Button 2" onAction="btnCentral" imageMso="FileSaveAsExcel97_2003" />
-
</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.
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
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
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
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'.
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?
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.
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?
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.
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
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?
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.
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