添加右键菜单命令

以下内容来自如何用vba在单元格右键快捷菜单中添加自定义的菜单命令

在右键菜单中添加一个叫做“测试”的选项，点击这一命令将执行’lyq’子过程。

Sub xyf()

    Dim objCB As CommandBar

    '获取单元格右键快捷菜单命令栏

    Set objCB = Excel.Application.CommandBars("Cell")

    Dim oCBC As CommandBarControl

    With objCB

        .Reset

        '往单元格右键快捷菜单中新增菜单命令

        Set oCBC = .Controls.Add(before:=1)

        With oCBC

            '指定单击新增的命令时执行的子过程名称

            .OnAction = "lyq"

            .Caption = "测试"

        End With

    End With

End Sub

Sub lyq()

    MsgBox 1

End Sub

添加二级右键菜单命令

以下内容来自使用VBA自定义添加Excel单元格右键菜单

使用以下代码将添加一个名为“Tools”的选项，其下含有两个子选项：Clean up和Conver to percent。单击可执行相应的过程。

'所有变量都需要先声明再使用
Option Explicit

'定义清除单元格控件Tools函数，保证如果之前如果已经添加Tools按钮
'那么会先清除之前的Tool按钮
Sub deletecellMenu()
    On Error Resume Next
    Application.CommandBars("cell").Controls("Tools").Delete
    On Error GoTo 0
End Sub

'定义添加单元格菜单函数
Sub addcellMenu()

    '定义PopupMenu
    Dim custMenu As CommandBar
    Dim subMenu As CommandBarControl



    '调用deletecellMenu函数
    deletecellMenu

    '创建Popup按钮
    Set custMenu = Application.CommandBars("cell")
    Set subMenu = custMenu.Controls.Add(msoControlPopup, , , 1)

    '对Popup按钮设置Caption(文字描述)
    With subMenu
        .Caption = "Tools"
        'Popup按钮添加子按钮
        With .Controls.Add(msoControlButton)
            .Caption = "Clean up"
            '指定时间对应的宏（函数）Clean_up
            .OnAction = "'" & ThisWorkbook.Name & "'!Clean_up"
            '设置图标
            .FaceId = 164
        End With
        
        With .Controls.Add(msoControlButton)
            .Caption = "Convert to Percent"
            .OnAction = "'" & ThisWorkbook.Name & "'!toPer"
            .FaceId = 59
        End With
        
    End With

    '将创建的对象subMenu设置为Nothing
    Set subMenu = Nothing
    Set custMenu = Nothing

End Sub

'定义Clean_up函数清空选中区域的格式和内容
Sub Clean_up()

    Dim item As Variant
    For Each item In Selection
        item.Clear
    Next
End Sub

'将选中单元格内容转化为百分比
Sub toPer()
    Dim item As Variant
    For Each item In Selection
        item.Value = Format(item.Value, "0.00%")
    Next
End Sub

删除自定义右键选项

以下内容来自Excel | 如何用VBA添加或删除右键快捷菜单

1. 列出右键选项

Sub 列出右键菜单()
    On Error Resume Next
    Dim mc As CommandBarControl
    x = 1
    Cells(x, 1) = "Index"
    Cells(x, 2) = "Caption"
    Cells(x, 3) = "ID"
    For Each mc In Application.CommandBars("cell").Controls
        Cells(x + 1, 1) = mc.Index
        Cells(x + 1, 2) = mc.Caption
        Cells(x + 1, 3) = mc.ID
        x = x + 1
    Next
End Sub

2. 删除右键选项

Sub 删除_芐雨()
Application.CommandBars("cell").Controls("显示拼音字段(&S)").Delete     '用名字识别删除
Application.CommandBars("cell").Controls(17).Delete                     'Index识别删除
End Sub

3. 添加右键选项

Sub 添加()
Application.CommandBars("cell").Controls.Add ID:=1614, before:=17   '在index17前面添加id为1614
End Sub

4. 重置右键选项

Sub 恢复()
'将内置 CommandBarControl 重置为其初始功能和图符，重置一个内置命令栏将删除其中的自定义控件并恢复其内置控件
Application.CommandBars("cell").Reset
End Sub