Create Icon Bar Drop Down for Sheet Selection

The CreateCB can be called from Workbook_Open and the Command bar is then activated in the ActivateSheet Event

Public Const strCBName As String = "SheetDemo"

Sub CreateCB()

Dim cbSheets As CommandBar

Dim cbcDrop As CommandBarControl

Dim shtO As Object

On Error Resume Next

Application.CommandBars(strCBName).Delete

On Error GoTo 0

Set cbSheets = Application.CommandBars.Add(strCBName)

Set cbcDrop = cbSheets.Controls.Add(msoControlDropdown)

For Each shtO In ThisWorkbook.Sheets

cbcDrop.AddItem shtO.Name

Next shtO

cbcDrop.OnAction = "ActivateSheet"

cbSheets.Visible = True

End Sub

Sub ActivateSheet()

ThisWorkbook.Sheets(Application.CommandBars(strCBName).Controls(1).Text).Activate

End Sub