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
To force an OnAction every time there is a selection, the Drop Down needs to be reset to a blank value after each selection. Add a blank value to the list of values in the Companies work sheet, as the first entry. Increase the count by one (5 to 6). Add this to the XML getLabel="DropDown_GetLabel". Add this to your OnAction call back to save the selected item index and then reset the control to default. Create the sSelectedItem as a global variable.
sSelectedItem = index + 1
updateRibbon
Add this to your Call Backs to value the Drop Down label with the currently selected item.
Public Sub DropDown_getLabel(control As IRibbonControl, ByRef label)
on error resume next ' to handle initial open of workbook
label = Worksheets("Companies").Cells(sSelectedItem, 2)
End Sub
and finally update the set default callback to select the zero (blank) entry.
Public Sub DropDown_getSelectedItemIndex(control As IRibbonControl, ByRef idx)
idx = 0 '<<<<
End Sub