Option Explicit
Dim objList As CommandBarControl
Dim ComBar
Const ComBarName As String = "Tabellenname"
Private Sub Auto_Open()
Dim n As Integer
'Falls Symbollsite schon existiert, löschen und erzeugen
On Error Resume Next
CommandBars(ComBarName).Delete
'Symbolleiste oben anzeigen, Button einfügen
Set ComBar = Application.CommandBars.Add(Name:=ComBarName, Position:=msoBarTop)
ComBar.Visible = True
Set objList = CommandBars(ComBarName).Controls.Add(Type:=msoControlDropdown)
With objList
.Width = 120 'Breite des TropDown
.OnAction = "Blatt_auswählen"
'Einträge im TropDown der Menüleiste
For n = 1 To Sheets.Count
If Sheets(n).Name = "Tabelle3" Then GoTo Sprungmarke 'Tabelle ausschließen
.AddItem Sheets(n).Name
Sprungmarke:
Next n
.ListIndex = 1
End With
End Sub
Sub Blatt_auswählen()
Set objList = CommandBars.ActionControl
Worksheets(objList.Text).Activate
End Sub
Private Sub Auto_Close()
On Error Resume Next
For Each ComBar In Application.CommandBars
If ComBar.Name = ComBarName Then ComBar.Delete
Next
End Sub