Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

UserForm-ComboBoxes als Collection-Objekte ansprechen

Gruppe

ComboBox

Problem

Wie kann ich eine Reihe von UserForm-ComboBoxes in ein CollectionObjekt aufnehmen und die Elemente als solche ansprechen?

Lösung
Geben Sie den nachfolgenden Code in das Klassenmodul der UserForm ein.

ClassModule: frmCollection

Private Sub cmdBeenden_Click()
   Unload Me
End Sub

Private Sub cmdColA_Click()
   Dim iCounter As Integer, iRow As Integer
   For iCounter = 1 To 4
      colA(iCounter).Clear
      For iRow = 1 To 12
         colA(iCounter).AddItem Format( _
            DateSerial(1, iRow, 1), "mmmm")
      Next iRow
      colA(iCounter).ListIndex = 0
   Next iCounter
End Sub

Private Sub cmdColALeeren_Click()
   Dim iCounter As Integer
   For iCounter = 1 To 4
      colA(iCounter).Clear
   Next iCounter
End Sub

Private Sub cmdColBLeeren_Click()
   Dim iCounter As Integer
   For iCounter = 1 To 4
      colB(iCounter).Clear
   Next iCounter
End Sub

Private Sub cmdColB_Click()
   Dim iCounter As Integer, iRow As Integer
   For iCounter = 1 To 4
      colB(iCounter).Clear
      For iRow = 1 To 7
         colB(iCounter).AddItem Format( _
            DateSerial(1, 1, iRow), "dddd")
      Next iRow
      colB(iCounter).ListIndex = 0
   Next iCounter
End Sub

Private Sub UserForm_Initialize()
   Dim iCounter As Integer
   On Error Resume Next
   For iCounter = 1 To 4
      colA.Remove iCounter
      colB.Remove iCounter
   Next iCounter
   On Error GoTo 0
   For iCounter = 1 To 8 Step 2
      colA.Add Controls("ComboBox" & iCounter)
      colB.Add Controls("ComboBox" & iCounter + 1)
   Next iCounter
End Sub

StandardModule: basMain

Public colA As New Collection
Public colB As New Collection

Sub CallForm()
   frmCollection.Show
End Sub