Gruppe
Menue
Problem
Beim Öffnen der Arbeitsmappe sollen in der Arbeitsblatt-Menüleiste zwei ComboBoxes mit den Inhalten der Spalten A:B angelegt werden.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Gruppe 1").Delete
.Controls("Gruppe 2").Delete
End With
End Sub
Private Sub Workbook_Open()
Dim oBar As CommandBar
Dim oCbo As CommandBarComboBox
Dim iCounter As Integer, iCol As Integer
Set oBar = Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
oBar.Controls("Gruppe 1").Delete
oBar.Controls("Gruppe 2").Delete
On Error GoTo 0
With oCbo
For iCol = 1 To 2
Set oCbo = oBar.Controls.Add(msoControlComboBox)
oCbo.Caption = "Gruppe " & iCol
For iCounter = 2 To 11
oCbo.AddItem Cells(iCounter, iCol).Value
oCbo.TooltipText = Cells(1, iCol).Value
Next iCounter
oCbo.ListIndex = 1
Next iCol
End With
End Sub