VBE.CommandBars

こんなの知らなかった・・・。

'vba
Option Explicit

Sub commandBar_test()
    Dim vbe As Object
    Set vbe = Application.vbe
    
    'CommandBar
    Dim cBar As Object
    Set cBar = get_commandBar("Custom1")

    'CommandControl
    Dim myControl
    For Each myControl In cBar.Controls
        Call myControl.Delete
    Next
    
    Set myControl = cBar.Controls _
        .Add(Type:=msoControlComboBox, before:=1)
    With myControl
        .AddItem Text:="First Item", Index:=1
        .AddItem Text:="Second Item", Index:=2
        .DropDownLines = 3
        .DropDownWidth = 75
        .ListHeaderCount = 0
    End With
    
End Sub


'https://learn.microsoft.com/ja-jp/office/vba/api/office.commandbars
Private Function get_commandBar( _
    ByVal name As String) As Object

    Dim vbe As Object
    Set vbe = Application.vbe
    
    On Error Resume Next
    
    Dim cmdBar As Object
    Set cmdBar = vbe.CommandBars.Item(name)
    
    On Error GoTo 0
    
    If cmdBar Is Nothing Then
        Set cmdBar = vbe.CommandBars.Add( _
            name:=name, _
            Position:=msoBarFloating _
        )
    End If
    cmdBar.Visible = True
    
    Set get_commandBar = cmdBar
    
End Function