Gruppe
Menue
Problem
Wie kann ich in die Arbeitsblattmenüleiste eine ComboBox mit Monatsnamen zur Auswahl von Monatsblättern einfügen?
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