Kapat

Create New Command Bar Using Macro

vba commandbar cell VBA Add A New Command Bar

In the template, we created a new command bar in the add-ins tab with the macro. We added two buttons and a drop-down list to this command bar.

We used VBA CommandBars.Add method to create a new command bar and to add it to the collection of command bars.

The code that follows creates a new toolbar (it contains two buttons and a drop-down list) that is displayed in the Add-Ins tab in the Custom Toolbars group :

Sub Workbook_Commandbar_Sample()
'Let's create the menubar
Dim sht As Worksheet
Dim MyBar As CommandBar
Dim MyButton, MyButton2 As CommandBarButton
Dim MyList As CommandBarComboBox

On Error Resume Next
Application.CommandBars("Sheet Navigate").Delete    'If the command bar was created before, it is deleted.
On Error GoTo 0

Set MyBar = CommandBars.Add("Sheet Navigate", , False, True)
With MyBar

Set MyButton = .Controls.Add(msoControlButton)      'Update button is being created.
With MyButton
            .Caption = "Update"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "Update_Lst"           'Macro name that triggered by the update button
End With

Set MyButton2 = .Controls.Add(msoControlButton)   'Sort button is being created.
With MyButton2
            .Caption = "A-Z"
            .Style = msoButtonCaption
            .BeginGroup = True
            .OnAction = "Sort"             'Macro name that triggered by the sort button
End With

Set MyList = .Controls.Add(msoControlDropdown)   'Drop-down list is being created.
With MyList
   For Each sht In ActiveWorkbook.Sheets     'With the loop,the worksheets are added to drop-down list.
   If sht.Visible = xlSheetVisible Then
    .AddItem "" & sht.Name
    .ListIndex = 1
   End If
   Next sht
   
.Tag = "List"
.TooltipText = "Sheet Navigate"
.OnAction = "Sheet_Navigate"            'Macro name that triggered by drop-down list
End With

.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With
Sheets(MyList.List(MyList.ListIndex)).Activate
End Sub

The workbook’s sheets are listed in the drop-down list. The selected sheet from the list becomes active. Thus, user can navigate easily between sheets of the workbook. excel vba commandbar

excel drop down list on command bar

vba add button to command bar With “Update” button in the command bar, the sheet names in the drop-down list are updated. In particular, this button needs to be clicked when a new sheet is added to the workbook or a sheet is deleted. excel vba commandbar button

VBA codes of Update button : excel vba command bar button

Sub Update_Lst()
Dim sht As Worksheet
Dim myControls As CommandBarControls

Application.CommandBars.FindControl(Tag:="List").Delete

Set MyList = CommandBars("Sheet Navigate").Controls.Add(msoControlDropdown)
With MyList
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = xlSheetVisible Then
 .AddItem "" & sht.Name
 .ListIndex = 1
End If
Next sht
 .Tag = "List"
 .OnAction = "Sheet_Navigate"
End With
Sheets(MyList.List(MyList.ListIndex)).Activate
End Sub

vba command bar button “Sort” button triggers the macro that sorted the sheet names in the dropdown list as alphabetically.

📥 Download sample workbook