如何让Excel Ribbon的toggleButton始终显示活动图表的ProtectFormatting状态？

问题分析 #

你原代码中用If Not TypeName(Selection) = "ChartArea" Then Exit Sub的判断逻辑存在缺陷：当选中图表的其他元素（比如数据系列、坐标轴）时，TypeName(Selection)不是ChartArea，但此时ActiveChart依然有效，导致按钮状态无法正确同步；另外如果没有任何活动图表时，直接访问ActiveChart会触发运行时错误。

修正后的代码 #

1. 保持原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="Group6" label="Protect Formatting">
          <toggleButton id="ToggleButton4" 
                        label="ChtLock" 
                        imageMso="Lock" 
                        size="large"
                        getPressed="ToggleButton4_Startup" 
                        onAction="ToggleButton4_OnAction"/>       
        </group>           
      </tab>
    </tabs>
  </ribbon>
</customUI>

2. 标准模块代码修正 #

Option Explicit
Public RibUI As IRibbonUI

Sub LoadRibbon(Ribbon As IRibbonUI)
    Set RibUI = Ribbon
    RibUI.InvalidateControl "ToggleButton4"
End Sub

' ToggleButton 状态获取
Sub ToggleButton4_Startup( _
   ByRef control As IRibbonControl, _
   ByRef returnedVal)
    ' 先判断是否存在活动图表
    If Not ActiveChart Is Nothing Then
        returnedVal = ActiveChart.ProtectFormatting
    Else
        ' 无活动图表时按钮置为未按下状态
        returnedVal = False
    End If
End Sub

' ToggleButton 点击事件
Sub ToggleButton4_OnAction( _
   ByRef control As IRibbonControl, _
   ByRef pressed As Boolean)
    ' 确保有活动图表时才执行操作
    If Not ActiveChart Is Nothing Then
        ActiveChart.ProtectFormatting = pressed
        ' 操作后刷新按钮状态
        RibUI.InvalidateControl "ToggleButton4"
    End If
End Sub

3. ThisWorkbook代码补充防护（避免RibUI未初始化报错） #

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Not RibUI Is Nothing Then
        RibUI.InvalidateControl "ToggleButton4"
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not RibUI Is Nothing Then
        RibUI.InvalidateControl "ToggleButton4"
    End If
End Sub

关键修正点 #

• 替换判断逻辑：不再依赖Selection的类型，直接检查ActiveChart Is Nothing，只要存在活动图表就同步状态，覆盖选中图表任意元素的场景。
• 错误防护：访问ActiveChart前先判断是否存在，避免无图表时触发运行时错误；同时给RibUI添加非空判断，防止加载前触发报错。
• 简化状态赋值：returnedVal = ActiveChart.ProtectFormatting直接等价于原逻辑，无需额外的= True判断。
• 操作后刷新：点击按钮修改设置后，主动调用InvalidateControl确保按钮状态立即同步。

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