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

CurtisD

New Member
Joined
Oct 4, 2019
Messages
15
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.

VBA Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,175,476
Messages
5,897,660
Members
434,667
Latest member
Ftdsa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top