Excel Ribbon ComboBox：实时显示当前选择并记忆保存前选项

针对你提出的两个问题，以下是具体实现方案：

一、修改Ribbon XML代码 #

需要给ComboBox添加getSelectedItemID回调，用于动态返回当前选中项的ID：

<group id="GroupDemo2" 
    label="SelectSheet"
    imageMso="AddInManager">    
    <comboBox id="ComboBox001"
        label="comboBox001"
        onChange="ComboBox001_OnChange"
        getSelectedItemID="ComboBox001_GetSelectedItemID">
        <item id="ItemOne"
            label="One"/>
        <item id="ItemTwo"
            label="Two"/>
        <item id="ItemThree"
            label="Three"/>
    </comboBox>
</group>

二、完整VBA代码实现 #

1. 标准模块代码 #

' 全局变量保存Ribbon对象，用于刷新界面
Public gRibbon As IRibbonUI

' Ribbon加载时初始化对象
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set gRibbon = ribbon
End Sub

' ComboBox选中变更回调
Sub ComboBox001_OnChange(control As IRibbonControl, id As String)
    Select Case id
        Case "ItemOne"
            Sheets("Sheet1").Select
        Case "ItemTwo"
            Sheets("Sheet2").Select
        Case "ItemThree"
            Sheets("Sheet3").Select
    End Select
    ' 保存选中ID到工作簿自定义属性
    SaveSelectedSheetID id
End Sub

' 返回当前选中工作表对应的ComboBox项ID
Sub ComboBox001_GetSelectedItemID(control As IRibbonControl, ByRef returnedVal)
    Select Case ActiveSheet.Name
        Case "Sheet1"
            returnedVal = "ItemOne"
        Case "Sheet2"
            returnedVal = "ItemTwo"
        Case "Sheet3"
            returnedVal = "ItemThree"
        Case Else
            returnedVal = "ItemOne" ' 默认选中第一个
    End Select
End Sub

' 保存选中ID到工作簿自定义属性
Sub SaveSelectedSheetID(sheetID As String)
    Dim prop As CustomProperty
    ' 检查是否已存在该属性
    On Error Resume Next
    Set prop = ThisWorkbook.CustomProperties("SelectedSheetID")
    On Error GoTo 0
    
    If prop Is Nothing Then
        ThisWorkbook.CustomProperties.Add Name:="SelectedSheetID", Value:=sheetID
    Else
        prop.Value = sheetID
    End If
End Sub

' 读取保存的选中ID
Function GetSavedSheetID() As String
    Dim prop As CustomProperty
    On Error Resume Next
    Set prop = ThisWorkbook.CustomProperties("SelectedSheetID")
    On Error GoTo 0
    
    If prop Is Nothing Then
        GetSavedSheetID = "ItemOne" ' 默认返回第一个
    Else
        GetSavedSheetID = prop.Value
    End If
End Function

2. 工作簿对象代码（ThisWorkbook） #

' 工作簿打开时，加载保存的选中状态并刷新Ribbon
Private Sub Workbook_Open()
    Dim savedID As String
    savedID = GetSavedSheetID()
    ' 根据保存的ID切换到对应工作表
    Select Case savedID
        Case "ItemOne"
            Sheets("Sheet1").Select
        Case "ItemTwo"
            Sheets("Sheet2").Select
        Case "ItemThree"
            Sheets("Sheet3").Select
    End Select
    ' 刷新Ribbon
    If Not gRibbon Is Nothing Then
        gRibbon.InvalidateControl "ComboBox001"
    End If
    ' 初始化工作表激活监控
    InitSheetMonitors
End Sub

' 工作簿关闭前保存当前选中状态
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim currentID As String
    Select Case ActiveSheet.Name
        Case "Sheet1"
            currentID = "ItemOne"
        Case "Sheet2"
            currentID = "ItemTwo"
        Case "Sheet3"
            currentID = "ItemThree"
    End Select
    SaveSelectedSheetID currentID
    ThisWorkbook.Save ' 保存工作簿，确保自定义属性被存储
End Sub

3. 工作表激活监控（类模块实现） #

为了避免逐个工作表添加代码，用类模块批量监控所有工作表的激活事件：

1. 插入一个类模块，命名为SheetMonitor
2. 在类模块中添加代码：

Public WithEvents MonitorSheet As Worksheet

Private Sub MonitorSheet_Activate()
    ' 工作表激活时刷新ComboBox
    If Not gRibbon Is Nothing Then
        gRibbon.InvalidateControl "ComboBox001"
    End If
End Sub

3. 在标准模块中添加初始化代码：

Dim sheetMonitors() As SheetMonitor

' 初始化工作表监控
Sub InitSheetMonitors()
    Dim ws As Worksheet
    Dim i As Integer
    ReDim sheetMonitors(1 To ThisWorkbook.Sheets.Count)
    
    For i = 1 To ThisWorkbook.Sheets.Count
        Set sheetMonitors(i) = New SheetMonitor
        Set sheetMonitors(i).MonitorSheet = ThisWorkbook.Sheets(i)
    Next i
End Sub

功能说明 #

• 问题A解决：通过工作表激活事件触发Ribbon控件刷新，getSelectedItemID回调会根据当前活动工作表返回对应的ComboBox项ID，确保显示与实际选中工作表一致。
• 问题B解决：将选中的ComboBox项ID存储在工作簿的自定义属性中，工作簿打开时读取该属性并切换到对应工作表，同时刷新Ribbon显示；关闭工作簿前自动保存当前选中状态。

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