Private Const cbName As String = "Custom CommandBar"

Sub CreateCommandBar()
    Dim cb As CommandBar
    Dim cbButton As CommandBarButton
    
    Set cb = Application.CommandBars.Add( _
      Name:=cbName, _
      Position:=msoBarTop, _
      MenuBar:=False, _
      Temporary:=True)
    
    Set cbButton = cb.Controls.Add( _
      Type:=msoControlButton, _
      Temporary:=True)
    
    With cbButton
        .Caption = "&Button1"
        .FaceId =  59 
        .Style = msoButtonIcon
        .Tag = "Button1"
        .OnAction = "Test"
        .TooltipText = "Кнопка с командой"
    End With
    
    cb.Visible = True
End Sub

Sub DeleteCommandBar()
    On Error Resume Next
    Application.CommandBars(cbName).Delete
    On Error GoTo  0 
End Sub

Sub Test()
    MsgBox "Макро"
End Sub

Private Sub Workbook_Open()
    CreateCommandBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteCommandBar
End Sub