I am trying to create a dynamically sized menu in a customised Ribbon tab (where the number of macros to include on the menu is defined in VBA not XML) and I can not get this to work.
It might be simpler to explain what I am after by demonstrating a similar concept (ignoring the customised tab aspect): I can create a XLAM containing grouped controls which appear in the Add-Ins tab. The number of macros to include the group are dynamically bound (sample below):
Popup1MacNames = Array("Macro1", "Macro2", "Macro3", "Macro4")
With Office.CommandBarControl
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "Caption1"
.BeginGroup = True
.Tag = C_TAG
For iCtr = LBound(Popup1MacNames) To UBound(Popup1MacNames)
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.OnAction = "'" & ThisWorkbook.Name & "'!" & Popup1MacNames(iCtr)
.Caption = Popup1MacNames(iCtr)
.Tag = C_TAG
End With
Next iCtr
End With
As can be seen with above code, to link a new macro to this group, all I need to do is type the sub name into the Array line.
Now I wish to do the equivalent in a customised ribbon tab. And I need to define the size of the menu in VBA, not in XML.
I am already using Bob Phillips Dynamic Ribbon code - I need to add a menu to contain a list of macros. And the ability to easily add extra macros to this menu without having to edit the XML every single time.
Bookmarks