如何为Excel指定区域创建自定义右键菜单并移除标准菜单项？

一、实现自定义右键菜单仅在指定区域（A1:B2）显示 #

无需在工作簿打开时创建全局菜单，通过工作表的BeforeRightClick事件动态判断点击位置，仅在目标区域弹出自定义菜单：

1. 右键目标工作表标签 → 选择「查看代码」，打开工作表代码模块
2. 粘贴以下代码：

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim targetRange As Range
    Set targetRange = Me.Range("A1:B2") ' 设置需要触发自定义菜单的区域
    
    ' 判断点击单元格是否在目标范围内
    If Not Intersect(Target, targetRange) Is Nothing Then
        Cancel = True ' 取消默认右键菜单
        
        ' 删除可能残留的旧菜单，避免重复创建
        On Error Resume Next
        CommandBars("MyCustomRightMenu").Delete
        On Error GoTo 0
        
        ' 创建临时自定义右键菜单
        Dim customMenu As CommandBar
        Set customMenu = CommandBars.Add(Name:="MyCustomRightMenu", Position:=msoBarPopup, Temporary:=True)
        
        ' 添加第一个宏关联项
        With customMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "MyMacro1"
            .OnAction = "MyMacro1" ' 关联对应的宏名称
        End With
        
        ' 添加第二个宏关联项
        With customMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "MyMacro2"
            .OnAction = "MyMacro2" ' 关联对应的宏名称
        End With
        
        ' 弹出自定义菜单
        customMenu.ShowPopup
    End If
End Sub

3. 保持原有的宏代码不变，将其放在标准模块中：

Public Sub MyMacro1()
    MsgBox "Macro1 from a right click menu"
End Sub

Public Sub MyMacro2()
    MsgBox "Macro2 from a right click menu"
End Sub

二、在目标区域移除标准右键菜单项 #

如果需要在点击A1:B2时隐藏指定的标准菜单项（如剪切、复制、粘贴），可在上述事件基础上添加状态保存与恢复逻辑，继续在目标工作表的代码模块中粘贴以下代码：

Dim savedControlStates As Collection ' 保存标准控件的原始可见状态

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim targetRange As Range
    Set targetRange = Me.Range("A1:B2")
    Set savedControlStates = New Collection
    
    If Not Intersect(Target, targetRange) Is Nothing Then
        Cancel = True
        ' 隐藏指定的标准菜单项
        HideSelectedStandardMenuItems
        
        ' 创建并显示自定义菜单（同第一部分逻辑）
        On Error Resume Next
        CommandBars("MyCustomRightMenu").Delete
        On Error GoTo 0
        
        Dim customMenu As CommandBar
        Set customMenu = CommandBars.Add(Name:="MyCustomRightMenu", Position:=msoBarPopup, Temporary:=True)
        
        With customMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "MyMacro1"
            .OnAction = "MyMacro1"
        End With
        
        With customMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "MyMacro2"
            .OnAction = "MyMacro2"
        End With
        
        customMenu.ShowPopup
        ' 菜单关闭后恢复标准控件状态
        RestoreStandardMenuItems
    Else
        ' 非目标区域，直接恢复标准控件
        RestoreStandardMenuItems
    End If
End Sub

' 隐藏指定的标准右键菜单项（根据Excel语言调整Caption文本）
Private Sub HideSelectedStandardMenuItems()
    Dim ctrl As CommandBarControl
    ' 中文Excel示例，英文版本需替换为"Cut", "Copy", "Paste"等
    For Each ctrl In CommandBars("Cell").Controls
        Select Case ctrl.Caption
            Case "剪切(&T)", "复制(&C)", "粘贴(&P)", "选择性粘贴(&S)..."
                ' 保存控件原始可见状态
                savedControlStates.Add Array(ctrl.Caption, ctrl.Visible)
                ctrl.Visible = False
        End Select
    Next ctrl
End Sub

' 恢复标准菜单项的原始可见状态
Private Sub RestoreStandardMenuItems()
    Dim item As Variant
    Dim ctrl As CommandBarControl
    On Error Resume Next
    If Not savedControlStates Is Nothing Then
        For Each item In savedControlStates
            For Each ctrl In CommandBars("Cell").Controls
                If ctrl.Caption = item(0) Then
                    ctrl.Visible = item(1)
                    Exit For
                End If
            Next ctrl
        Next item
        Set savedControlStates = Nothing
    End If
End Sub

' 切换选区时自动恢复标准菜单
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    RestoreStandardMenuItems
End Sub

注意事项 #

• 不同语言版本的Excel，标准菜单项的Caption文本不同，需根据实际情况调整
• 临时菜单（Temporary:=True）会在Excel关闭时自动删除，避免残留
• 测试前请将工作簿保存为启用宏的格式（.xlsm）

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