With the new release of OfficeWriter 8.1’s designer ribbon, I’ve been working more with the ribbon interface. This is a follow up post to one written previously on updating an old Office Add-In to use the new ribbon interface. In this post we’ll explore how to create dynamic menus in VBA Add-Ins.
Once you have the ribbon for a VBA Add-In, there are a number of things you can add to make the UI more useful. One of the more versatile elements available is the dynamic menu. This menu lets you change it during runtime! Unfortunately, working with it in VBA is a little strange.
First add the base xml to the ribbon xml like you would for any UI element.
…
<group label=”Ribbon Group” id=”group1”>
…
<dynamicMenu label=”Dynamic Menu” id=”menu1″
imageMso=”TableInsert” getContent=”ReloadMenu”/>
</group>
…
The important attribute here is “getContent”. This is a callback to a method in the VBA which will create the items for the menu. The method will literally create xml for a menu from scratch and return it. Anything that can normally be in a menu can be added this way – including buttons, submenus, and separators. The sample code below creates a button and a submenu with another button inside it.
Sub ReloadMenu(control As IRibbonControl, ByRef content)
Dim xml As String
xml = “<menu xmlns=”http://schemas.microsoft.com/office/2006/01/customui””>”
xml = xml & “<button id=””button1”” label=””FirstButton”” onAction=””ButtonClick””/>”
xml = xml & “<menu id=””submenu1”” label=””Submenu””>”
xml = xml & “<button id=””button2”” label=””SecondButton”” onAction=””ButtonClick””/>”
xml = xml & “</menu>”
xml = xml & “</menu>”
content = xml
End Sub
For loops are very useful if many buttons need to be made, but be careful that you keep all ids unique. Any problems in the XML will result in a blank menu and no useful information.
There is one last step to getting the dynamic menu working. “ReloadMenu” will only be called after the menu has been invalidated.
Make sure you have an onLoad callback for your ribbon.
<customUI onLoad=”OnLoad” xmlns=http://schemas.microsoft.com/office/2006/01/customui>
Within that method, create a ribbon variable.
Private gRibbon As IRibbonUI
Sub OnLoad(ribbon As IRibbonUI)
Set gRibbon = ribbon
End Sub
And whenever something happens that will change the dynamic menu, make sure you call invalidate on it.
gRibbon.InvalidateControl “menu1”
Invalidating controls also allows other dynamic properties to take effect, such as getEnabled and getVisible.
Follow