Excel – How to dynamically populate a menu control on a custom ribbon

excelopenxmlribbonvba

For Each ws In Worksheets
ListBox1.AddItem ws.Name
Next ws

ListBox1_Click()
Worksheets(ListBox1.Value).Activate

Could I have the same thing with a menu control on a custom ribbon
xml:

<menu id="mnuCats" label="abc" size="large" imageMso="PropertySheet"  >
<button id="go01" label="sky"  onAction="go01" imageMso="TextAlignGallery" />
<button id="go02" label="sea"  onAction="go02" imageMso="TextAlignGallery" />
</menu>

So, how to populate the menu with sheet names and how to get label attribute of a button as a string, for activating corresponding sheet.

Best Answer

You need a dropdown ribbon control. If you enter the following customUI XML in your Excel file (maybe by using the Custom UI Editor http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/25/customuieditor.aspx) you can get a list of the correct VBA callback signatures you need to implement in order to fill the values and respond to actions:

<dropDown id="drpTest" label="Test" showImage="false" getSelectedItemIndex="TestGetSelItem" onAction="TestOnAction" getItemCount="TestGetItemCount" getItemID="TestGetItemID" getItemLabel="TestGetItemLabel" sizeString="HowBigDropdownDoYouWant" supertip="Select Test.">
</dropDown>
Related Topic