Option Explicit
Sub Auto_Close()
On Error Resume Next
Application.CommandBars("Sheet_Navigator").Delete
On Error GoTo 0
End Sub
Sub Auto_Open()
Dim cb As CommandBar
Dim ctrl As CommandBarControl
On Error Resume Next
Application.CommandBars("Sheet_Navigator").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add(Name:="Sheet_Navigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh List"
.OnAction = ThisWorkbook.Name & "!Refresh"
End With
Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.Width = 150
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!Change_Sheet"
.Tag = "__wksnames__"
End With
End With
Call Refresh
End Sub
Sub Change_Sheet()
Dim myWksName As String
Dim wks
With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With
Set wks = Nothing
On Error Resume Next
Set wks = Sheets(myWksName)
On Error GoTo 0
If wks Is Nothing Then
Call Refresh
MsgBox "Please try again"
Else
wks.Select
End If
End Sub
Sub Refresh()
Dim ctrl As CommandBarControl
Dim wks
On Error GoTo Finish:
Set ctrl = Application.CommandBars("Sheet_Navigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear
On Error Resume Next
For Each wks In ActiveWorkbook.Sheets
If wks.Visible = xlSheetVisible Then
ctrl.AddItem wks.Name
End If
Next wks
Finish:
End Sub