Excel Custom Ribbon Drop Down Element - How to View Id, Index, Label for Each Item

[CurtisD]

I have created a custom ribbon for an Excel workbook. I used the Custom UI Editor for Microsoft Office
to create the XML. I also used the Custom UI Editor to generate callbacks. I modified the callbacks slightly
for my workbook. The custom ribbon has just one element - a dropdown.

The Excel data that I used to populate the dropdown is below, in worksheet "Companies". The rows are in sorted order, by company name.
The first and last rows are fictitious companies, and just there to help my debugging.

1/1/2022 AAAAA 0
1/15/2022 ABC CO Pencils 100
1/31/2022 DEF CO Paper 25
2/1/2022 XYZ INC Pens 120
1/1/2022 ZZZZZ 0

The drop down menu displays the company name. In the callback DropDown_getSelectedItemID() I specified Cells(2,2), which is "ABC CO",
so that the drop down would be set to that ID by default.

There is clearly a problem. If I use the drop down to select "ABC CO", which is already selected in the drop down, the onAction event
does not fire. I know that because I print a message when the onAction event fires. But if I select the next row, DEF CO",
onAction does fire.

It would be very helpful if I could view the index, ID and label for each of items in the drop down. It might show me that
the ID is null, or indicate the problem. But I don't know how to view that data for each item. I would be very grateful for help on that.

Below are the callbacks I use. If anyone sees an error in them I hope you will let me know. Thanks very much.

Option Explicit
'testRibbon is a variable which contains the Ribbon
Public testRibbon As IRibbonUI

'Callback for customUI.onLoad
Public Sub testRibbon_onLoad(ribbon As IRibbonUI)
   Set testRibbon = ribbon
End Sub

'Callback for DropDown getItemCount
Public Sub DropDown_getItemCount(control As IRibbonControl, ByRef returnedVal)
   returnedVal = 5
End Sub

'Callback for DropDown getItemLabel
Public Sub DropDown_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
   returnedVal = Worksheets("Companies").Cells(index + 1, 2)
End Sub

'Callback for DropDown onAction
Public Sub DropDown_onAction(control As IRibbonControl, id As String, index As Integer)
   MsgBox index + " was selected"
End Sub

Public Sub DropDown_getItemID(control As IRibbonControl, index As Integer, ByRef id)
   'This Callback will set the id for each item created.
   'It provides the index value within the Callback.
   'The index is the position within the drop-down list.
   id = Worksheets("Companies").Cells(index + 1, 2)
End Sub

Public Sub DropDown_getSelectedItemID(control As IRibbonControl, ByRef id)
   'This Callback will change the drop-down to be set to a specific id.
   'This could be used to set a default value or reset the first item in the list
   id = Worksheets("Companies").Cells(2, 2)
End Sub

Public Sub updateRibbon()
'This is a standard procedure, not a Callback.  It is triggered by the button.
'It invalidates the Ribbon, which causes it to re-load.

On Error Resume Next
   testRibbon.Invalidate
On Error GoTo 0

End Sub