This is a follow-up question to this one:
Excel Ribbon comboBox: How to set up a getSelectedItemIndex Callback
The answer to that revealed:
- Call back
getSelectedItemIndex
is available fordropDown
instead ofcomboBox
.
Question: Is there any other way to get the index of the selection in a comboBox?
This answer uses a Dictionary to build the onChange Callback.
Would this be the way to go?
comboBox:
' -- XML
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadRibbon">
<ribbon>
<tabs>
<tab id="Tabv3.1" label="TOOLS" insertAfterMso="TabHome">
<group id="GroupDemo2"
label="SelectPapersize"
imageMso="AddInManager">
<comboBox id="ComboBox001"
label="comboBox001"
getText="ComboBox001_GetText"
onChange="ComboBox001_OnChange">
<item id="Item_A3"
label="A3"/>
<item id="Item_A4"
label="A4"/>
<item id="Item_A5"
label="A5"/>
</comboBox>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
' -- Callback VBA in Module "RibbonCallbacks"
Option Explicit
Public RibUI As IRibbonUI
Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"
Sub LoadRibbon(Ribbon As IRibbonUI)
Set RibUI = Ribbon
RibUI.InvalidateControl "ComboBox001"
End Sub
'Callback for ComboBox001 onChange
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
Select Case id
Case "A3"
ActiveSheet.PageSetup.PaperSize = xlPaperA3
Case "A4"
ActiveSheet.PageSetup.PaperSize = xlPaperA4
Case "A5"
ActiveSheet.PageSetup.PaperSize = xlPaperA5
End Select
RibUI.InvalidateControl "ComboBox001"
SaveSetting myApp, mySett, myVal, id
End Sub
'Callback for ComboBox001 getText
Sub ComboBox001_getText(control As IRibbonControl, ByRef returnedVal)
Dim comboVal As String
comboVal = GetSetting(myApp, mySett, myVal, "No Value")
If comboVal <> "No Value" Then
returnedVal = comboVal
End If
End Sub
' -- VBA in "ThisWorkbook"
Private Sub Workbook_Open()
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim idx As String
Select Case PaperSize
Case "xlPaperA3"
idx = "A3"
Case "xlPaperA4"
idx = "A4"
Case "xlPaperA5"
idx = "A5"
End Select
SaveSetting myApp, mySett, myVal, idx
RibUI.InvalidateControl "ComboBox001"
End Sub
onChange CallBack using a Dictionary:
'/questions/60453051/ribbon-xml-get-selected-index-or-item-id-in-a-combobox-control
<customUI onLoad="Ribbon_Load" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="SearchCustomerTab" insertAfterMso="TabAddIns" label="Cliente" visible="true">
<group id="SearchCustomerGroup" label="Cliente" autoScale="true">
<comboBox id="CustomerComboBox" getItemCount="GetItemCountCallback" getItemLabel="GetItemLabelCallback" getItemID="GetItemIDCallback" onChange="OnChangeCallback" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
rivate customClass As CustomClass
Private customDictionary As Dictionary(Of String, CustomClass)
Public Sub Ribbon_Load(ByVal ribbonUI As Office.IRibbonUI)
Dim customList As List(Of CustomClass)
customList = FunctionToPopulateMyList()
customDictionary = customList.ToDictionary(Function(p) p.MyText, Function(p) p)
End Sub
Public Function GetItemLabelCallback(ByVal control As Office.IRibbonControl, index As Integer) As String
Return oCustomDictionary.ElementAt(index).Value.MyText
End Function
Public Function GetItemCountCallback(ByVal control As Office.IRibbonControl) As Integer
Return oCustomDictionary.Count
End Function
Public Function GetItemIDCallback(ByVal control As Office.IRibbonControl, index As Integer) As String
Return "Item" & index.ToString & control.Id
End Function
Public Sub OnChangeCallback(ByVal control As Office.IRibbonControl, text As String)
If (customDictionary.ContainsKey(text)) Then
customClass = customDictionary(text)
End If
End Function