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.
[xml]
[/xml]
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:
[vb]Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
'Populate a menu item
Dim sXML As String
'Open the XML string
sXML = "
"
'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[/vb]
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:
[xml]
[/xml]
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 thoughts on “Modifying the Ribbon – Part 8”
instead of a "MsgBox" i want to be able to select page in my workbook ,,,,,,help me please 🙂
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
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
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'.
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?
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.
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?
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.
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
Hi Kevin,
Out of curiosity, is the Add-in loaded when you open the document in this way?
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.
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