I have, in ExcelDNA, created a custom ribbon (extending ExcelRibbon) and overridden the GetCustomUI() method to create a menu control from a list of strings. Basically:
public override string GetCustomUI(string RibbonID)
{
string customUIXml =
@"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage' onLoad='OnRibbonLoaded' >
<ribbon>
<tabs>
<tab id='CustomTab' label='My Dynamic Tab'>
<group id='SampleGroup' label='My Sample Group'>
<menu description='description' enabled='true' id='menuItem' visible='true' size='normal' >";
foreach (string itemName in _ItemNameList)
customUIXml += $"<button id='btn_tool_{itemName}' label='{itemName}' onAction='MyMethod' />";
customUIXml +=
@"</menu>
</group >
</tab>
</tabs>
</ribbon>
</customUI>";
return customUIXml;
}
Because _ItemNameList
is retrieved from a different file/system, I can't place this customUI tag directly into the .dna file (as far as I know) and hence I build it on load via the GetCustomUI().
As a proof of concept, the onAction
method will add a new item to _ItemNameList
when the user clicks on a menu item.
But how do I get Excel to call GetCustomUI again in order to have the XML rebuilt?
I have tried invalidating the ribbon object itself, but this does not trigger Excel to call GetCustomUI again.
The next best thing I can think of (although I'm still to test this) is to create a menu with a whole load of invisible 'placeholder' buttons (by specifying the getVisible callbacks) and when the user clicks on a button, just invalidate the button that needs to now become visible, showing the new item added to _ItemNameList
. I still need to think through how to get the correct button reference though... This also feels a little dirty.
Any ideas how to have Excel rebuild the ribbon? Also open to other ideas to allow me to add items to a menu in an ExcelRibbon.
I think you are looking for the
dynamicMenu
control, which will trigger an event to let you dynamically set the contents of the menu, every time you click on it.Something like this:
Your
OnGetContent
method will get fired every time the user clicks on the ribbon button, so you can dynamically assemble the items.e.g.
Of course, you'd have to build this string dynamically from your list, etc.