如何获取Excel Ribbon ComboBox选中项的索引？

问题背景 #

此前问题确认getSelectedItemIndex回调仅支持dropDown控件，无法用于ComboBox。本次探讨两个核心问题：

• 是否有办法获取Excel Ribbon ComboBox中选中项的索引？
• 两种实现思路的可行性：
  1. 用Dictionary(Of String, CustomClass)构建onChange回调的方案
  2. 利用官方文档中的getItemID属性实现需求

现有ComboBox配置与VBA回调代码 #

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>

VBA回调代码（模块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

ThisWorkbook中的代码 #

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim idx As String
    Select Case Sh.PageSetup.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

两种实现思路的可行性分析 #

1. Dictionary(Of String, CustomClass)方案：完全可行 #

这个方案的核心是用字典建立选项文本与自定义对象的映射，自定义对象可存储索引、ID等额外信息，在onChange回调中通过选中的文本直接获取对应对象，进而拿到索引。

调整后的示例代码（修正原示例问题）

需先引用Microsoft Scripting Runtime（工具→引用），或使用后期绑定：

' 自定义类模块：CustomClass
Public MyText As String
Public Index As Integer ' 存储选项索引

' 回调模块代码
Private customDict As Dictionary
Private RibUI As IRibbonUI

Public Sub Ribbon_Load(ByVal ribbonUI As Office.IRibbonUI)
    Set RibUI = ribbonUI
    Set customDict = New Dictionary
    
    ' 模拟填充选项列表
    Dim item1 As New CustomClass
    item1.MyText = "A3"
    item1.Index = 0
    customDict.Add item1.MyText, item1
    
    Dim item2 As New CustomClass
    item2.MyText = "A4"
    item2.Index = 1
    customDict.Add item2.MyText, item2
    
    Dim item3 As New CustomClass
    item3.MyText = "A5"
    item3.Index = 2
    customDict.Add item3.MyText, item3
    
    RibUI.InvalidateControl "CustomerComboBox"
End Sub

Public Function GetItemCountCallback(ByVal control As Office.IRibbonControl) As Integer
    GetItemCountCallback = customDict.Count
End Function

Public Function GetItemLabelCallback(ByVal control As Office.IRibbonControl, index As Integer) As String
    ' 按索引取出字典中的项
    GetItemLabelCallback = customDict.Items()(index).MyText
End Function

Public Function GetItemIDCallback(ByVal control As Office.IRibbonControl, index As Integer) As String
    GetItemIDCallback = "Item" & index & "_" & control.Id
End Function

Public Sub OnChangeCallback(ByVal control As Office.IRibbonControl, text As String)
    If customDict.Exists(text) Then
        Dim selectedItem As CustomClass
        Set selectedItem = customDict(text)
        ' 获取选中项的索引
        MsgBox "选中项索引：" & selectedItem.Index
        ' 后续逻辑...
    End If
End Sub

2. getItemID属性方案：完全可行 #

getItemID用于为ComboBox的动态项生成唯一ID（静态项使用XML中定义的id）。我们可以在生成ID时嵌入索引信息，再在onChange回调中解析ID提取索引。

针对静态项的实现（以原有纸张大小ComboBox为例）

利用XML中已定义的静态项ID，预先建立ID与索引的映射：

Option Explicit
Public RibUI As IRibbonUI
Public Const myApp As String = "RibbApp", mySett As String = "Settings", myVal As String = "Value"
Private itemIndexMap As Dictionary ' 存储ID到索引的映射

Sub LoadRibbon(Ribbon As IRibbonUI)
    Set RibUI = Ribbon
    Set itemIndexMap = New Dictionary
    ' 建立ID与索引的映射
    itemIndexMap.Add "Item_A3", 0
    itemIndexMap.Add "Item_A4", 1
    itemIndexMap.Add "Item_A5", 2
    RibUI.InvalidateControl "ComboBox001"
End Sub

'Callback for ComboBox001 onChange
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Dim selectedIndex As Integer
    ' 从映射中获取索引
    selectedIndex = itemIndexMap(id)
    MsgBox "选中项索引：" & selectedIndex
    
    ' 原有逻辑...
    Select Case id
        Case "Item_A3"
             ActiveSheet.PageSetup.PaperSize = xlPaperA3
        Case "Item_A4"
            ActiveSheet.PageSetup.PaperSize = xlPaperA4
        Case "Item_A5"
            ActiveSheet.PageSetup.PaperSize = xlPaperA5
    End Select
    RibUI.InvalidateControl "ComboBox001"
    SaveSetting myApp, mySett, myVal, Mid(id, 6) ' 提取A3/A4/A5存储
End Sub

针对动态项的实现

在getItemID中直接嵌入索引，再在onChange回调中拆分ID提取：

Public Function GetItemIDCallback(ByVal control As Office.IRibbonControl, index As Integer) As String
    GetItemIDCallback = "Item_" & index
End Function

Public Sub OnChangeCallback(ByVal control As Office.IRibbonControl, id As String)
    ' 拆分ID获取索引
    Dim selectedIndex As Integer
    selectedIndex = CInt(Split(id, "_")(1))
    MsgBox "选中项索引：" & selectedIndex
End Sub

总结 #

获取Excel Ribbon ComboBox选中项索引的核心是建立选项标识（文本/ID）与索引的映射关系，两种方案均可行：

• 静态项：用字典预先存储ID/文本与索引的对应关系，在onChange中直接查找。
• 动态项：结合getItemID嵌入索引信息，或用Dictionary存储文本与含索引的自定义对象的映射。

内容的提问来源于stack exchange，提问作者user23636411