Excel VBA：实现右键菜单临时选项仅在指定工作表可用时的代码报错求助

问题背景 #

我在名为“SPG Summary”的工作表中编写了VBA代码，想要在右键菜单里添加一个临时选项，且这个选项只能在“SPG Summary”工作表中显示使用。相关代码如下：

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmdBtn As CommandBarButton, param As String
    Set cmdBtn = Application.CommandBars("Cell").FindControl(, , "testBt")
    If Intersect(Target, Range("C21:C42")) Is Nothing Then
        If Not cmdBtn Is Nothing Then cmdBtn.Delete
        Exit Sub
    End If
    If Not cmdBtn Is Nothing Then Exit Sub
    Set cmdBtn = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
    param = "test param"
    With cmdBtn
        .Tag = "testBt"
        .Caption = "MyOption"
        .Style = msoButtonCaption
        .OnAction = "TestMacro"
    End With
End Sub

Private Sub Worksheet_Deactivate()
    Dim cmdBtn As CommandBarButton
    Set cmdBtn = Application.CommandBars("Cell").FindControl(, , "testBt")
    If Not cmdBtn Is Nothing Then cmdBtn.Delete
End Sub

对应的执行宏代码：

Sub TestMacro()
    Sheets("UPC Summary").Range("A21").Value = ActiveCell.Value
    Application.Goto Sheets("UPC Summary").Range("A1"), True
End Sub

遇到的问题：

若移除Worksheet_Deactivate过程，代码能正常运行，但右键菜单选项会跟着切换到其他工作表，不符合我只让它在“SPG Summary”显示的需求。但保留这个过程时，Excel会把If Not cmdBtn Is Nothing Then cmdBtn.Delete这一行标为错误，特此求助。

报错原因分析 #

这个错误的核心原因是：在Worksheet_Deactivate事件触发时，Excel的命令栏控件可能处于不可访问状态，或者FindControl返回的控件引用已经失效，直接执行Delete操作会触发异常。另外，切换工作表时的命令栏状态变化也可能导致控件引用不一致。

解决方案 #

我们可以通过添加错误处理机制优化控件删除逻辑，同时调整右键菜单的初始化逻辑，确保操作安全且符合需求。

修正后的完整代码 #

在“SPG Summary”工作表的代码模块中，替换为以下代码：

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmdBtn As CommandBarButton
    ' 先清理已存在的目标按钮，避免重复添加
    Set cmdBtn = Application.CommandBars("Cell").FindControl(Tag:="testBt")
    If Not cmdBtn Is Nothing Then cmdBtn.Delete
    
    ' 仅在指定区域右键时添加菜单选项
    If Not Intersect(Target, Range("C21:C42")) Is Nothing Then
        Set cmdBtn = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        With cmdBtn
            .Tag = "testBt"
            .Caption = "MyOption"
            .Style = msoButtonCaption
            .OnAction = "TestMacro"
        End With
    End If
End Sub

Private Sub Worksheet_Deactivate()
    Dim cmdBtn As CommandBarButton
    ' 添加错误处理，忽略删除时可能出现的控件引用异常
    On Error Resume Next
    Set cmdBtn = Application.CommandBars("Cell").FindControl(Tag:="testBt")
    If Not cmdBtn Is Nothing Then cmdBtn.Delete
    ' 恢复默认错误处理机制
    On Error GoTo 0
End Sub

TestMacro代码保持不变即可。

代码优化说明 #

1. 前置清理旧按钮：在每次右键菜单弹出前先删除已存在的目标按钮，避免多次右键点击导致菜单重复添加，同时保证菜单状态的一致性。
2. 添加错误处理：在Worksheet_Deactivate中加入On Error Resume Next，可以忽略删除控件时可能出现的引用失效或不可访问错误，避免Excel弹出报错窗口。
3. 精准查找控件：使用FindControl(Tag:="testBt")明确通过Tag属性定位控件，比原代码的模糊查找更精准，避免误删其他无关控件。

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