VBA-Programmierung in Microsoft Excel

ComboBox für Monatsblattauswahl in Menüleiste 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

    

Beiträge aus dem Excel-Forum zu den Themen Menue und ComboBox