如何用vba给菜单添加各种类型的自定义控件命令？

自定义菜单控件可以有多种类型，常见的有CommandBarButton、CommandBarComboBox和CommandBarPopup。

利用CommandBarControls.Add方法可以添加各种类型的自定义菜单命令。

它的语法如下：

expression. Add( Type, Id, Parameter, Before, Temporary )

其中Type参数为要添加的控件类型，可以是msoControlButton, msoControlEdit, msoControlDropdown, msoControlComboBox, 或者 msoControlPopup

以下是一个在“加载项”选项卡下的“菜单命令”中添加各种类型的控件的代码：

Sub QQ1722187970()
    '命令栏
    Dim oCB As CommandBar
    '通用的命令栏控件
    Dim oCBC As CommandBarControl
    '命令按钮控件
    Dim oCBB As CommandBarButton
    '组合框控件
    Dim oCBCom As CommandBarComboBox
    '组合框控件
    Dim oCBCom1 As CommandBarComboBox
    '弹出式菜单
    Dim oCBPop As CommandBarPopup
    For Each oCB In Excel.Application.CommandBars
       oCB.Reset
    Next
    '添加加载项选项卡下的菜单命令组中的命令
    Set oCB = Excel.Application.CommandBars("Worksheet Menu Bar")
    With oCB
        .Reset
        '添加命令按钮控件
        Set oCBB = .Controls.Add(msoControlButton)
        With oCBB
            .Caption = "第一个控件"
           .FaceId = 18
        End With
         '添加文本框控件
        Set oCBCom = .Controls.Add(msoControlEdit)
        With oCBCom
            '设置文本框的默认值
           .Text = "这里显示默认值"
        End With
'         '添加下拉列表框控件
        Set oCBCom1 = .Controls.Add(msoControlDropdown)
        With oCBCom1
           .AddItem "张三"
           .AddItem "李四"
           .AddItem "王五"
        End With
         '添加弹出式菜单
        Set oCBPop = .Controls.Add(msoControlPopup)
        With oCBPop
            .Caption = "弹出式菜单"
            With .Controls.Add
                .Caption = "第二个控件"
                .FaceId = 19
            End With
        End With
    End With
End Sub