Gruppe
Menue
Bereich
ComboBox
Thema
ComboBox für Monatsblattauswahl in Menüleiste einfügen
Problem
Wie kann ich in die Arbeitsblattmenüleiste eine ComboBox mit Monatsnamen zur Auswahl von Monatsblättern einfügen?
Lösung
Geben Sie den Ereigniscode in das Klassenmodul der Arbeitsmappe ein.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CmdDelete
End Sub
Private Sub Workbook_Open()
Dim oCbo As CommandBarComboBox
Dim wks As Worksheet
Dim iCounter As Integer
Call CmdDelete
With Application.CommandBars("Worksheet Menu Bar")
Set oCbo = .Controls.Add( _
Type:=msoControlComboBox, before:=.Controls.Count)
End With
With oCbo
.Caption = "Monate"
.OnAction = "BlattAuswahl"
For iCounter = 0 To 11
.AddItem Format(DateSerial(1, iCounter + 1, 1), "mmmm")
Next iCounter
.ListIndex = 1
End With
End Sub
StandardModule: basMain
Sub JahrAnlegen()
Dim iMonat As Integer
For iMonat = 1 To 12
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(DateSerial(1, intMonat, 1), "mmmm")
ActiveSheet.Visible = xlSheetVeryHidden
Next iMonat
End Sub
Sub Blattauswahl()
Call Verbergen
With Worksheets(CommandBars("Worksheet Menu Bar") _
.Controls("Monate").ListIndex + 2)
.Visible = True
.Select
End With
End Sub
Sub Anzeigen()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Visible = True
Next wks
End Sub
Sub Verbergen()
Dim iCounter As Integer
For iCounter = 3 To Worksheets.Count
Worksheets(iCounter).Visible = xlVeryHidden
Next iCounter
End Sub
Sub CmdDelete()
On Error GoTo ERRORHANDLER
Application.CommandBars("Worksheet Menu Bar") _
.Controls("Monate").Delete
ERRORHANDLER:
End Sub