Wie erstellt man dynamische kaskadierende Listenfelder in Excel?

Sie wissen vielleicht, wie man eine kaskadierende Validierungs-Dropdown-Liste in Excel erstellt. Aber wie erstellt man dynamische kaskadierende Listenfelder in Excel? Dieser Artikel stellt eine VBA-Methode vor, um dies zu erreichen.

Dynamische kaskadierende Listenfelder mit VBA-Code erstellen

Dynamische kaskadierende Listenfelder mit VBA-Code erstellen

Wie im folgenden Screenshot gezeigt, müssen Sie ein übergeordnetes Listenfeld erstellen, das die eindeutigen Werte der Spalte „Getränk“ enthält, und alle entsprechenden Werte im zweiten Listenfeld basierend auf der Auswahl im übergeordneten Listenfeld anzeigen. Der folgende VBA-Code hilft Ihnen dabei. Bitte folgen Sie den nachstehenden Schritten.

1. Zuerst müssen Sie alle eindeutigen Werte aus der Spalte „Getränk“ extrahieren. Wählen Sie eine leere Zelle aus, geben Sie die Array-Formel =WENNFEHLER(INDEX($A$2:$A$11; VERGLEICH(0;ZÄHLENWENN($J$1:J1; $A$2:$A$11); 0));"") in die Formel-Leiste ein und drücken Sie dann die Tastenkombination Strg + Umschalt + Eingabetaste. Ziehen Sie dann den Ausfüllkästchen-Balken, um alle eindeutigen Werte zu erhalten. Siehe Screenshot:

Hinweis: In der Formel ist $A$2:$A$11 der Bereich, aus dem Sie eindeutige Werte extrahieren möchten. J1 ist die Zelle oberhalb der Position, an der sich Ihre Formel befindet.

Tipp: Wenn die Formel zu schwer zu merken und zu handhaben ist, ist das Hilfsprogramm „Doppelte & Eindeutige Zellen auswählen“ von Kutools für Excel eine gute Wahl, um schnell alle eindeutigen Werte aus einer Spalte zu extrahieren.

Bitte wählen Sie die Spalte aus, die die eindeutigen Werte enthält, die Sie extrahieren möchten. Aktivieren Sie das Hilfsprogramm, indem Sie auf Kutools > Auswählen > Doppelte & Eindeutige Zellen auswählen klicken. Im Dialogfeld „Doppelte & Eindeutige Zellen auswählen“ wählen Sie die Option „Alle eindeutigen (einschließlich des ersten Duplikats)“ aus und klicken Sie auf die Schaltfläche OK. Dann werden alle eindeutigen Werte in der Spalte ausgewählt. Kopieren Sie diese und fügen Sie sie an einen neuen Ort ein. Siehe Screenshot:

2. Fügen Sie zwei Listenfelder separat ein, indem Sie auf Entwickler > Einfügen > Listenfeld (ActiveX-Steuerelement) klicken. Siehe Screenshot:

3. Klicken Sie mit der rechten Maustaste auf das übergeordnete Listenfeld und wählen Sie Eigenschaften aus dem Kontextmenü. Ändern Sie im Eigenschaften-Dialogfeld das Feld (Name) in „Drink“ oder einen anderen Namen, den Sie benötigen, geben Sie den Zellbereich mit den extrahierten eindeutigen Werten in das Feld ListFillRange ein und schließen Sie das Dialogfeld.

4. Wiederholen Sie Schritt 3, um das Feld (Name) des zweiten Listenfelds im Eigenschaften-Dialogfeld in „Item“ zu ändern.

5. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie Code anzeigen aus dem Rechtsklick-Menü. Kopieren Sie dann den folgenden VBA-Code in das Codefenster. Siehe Screenshot:

VBA-Code: Dynamische kaskadierende Listenfelder in Excel erstellen

Dim xPreStr As String
Private Sub Drink_Click()
'Update by Extendoffice 2018/06/04
    Dim I, xRows As Long
    Dim xRg As Range
    Dim xRegStr As String
    Application.ScreenUpdating = False
    xRegStr = Me.Drink.Text
    Set xRg = Range("A2:A11")
    xRows = xRg.Rows.Count
    If xRegStr <> xPreStr Then
    Me.Item.Clear
   'Me.OtherListBoxName.Clear
    Set xRg = xRg(1)
    For I = 1 To xRows
        If xRg.Offset(I - 1).Value = xRegStr Then
            Me.Item.AddItem xRg.Offset(I - 1, 1).Value
            'Me.OtherListBoxName.AddItem xRg.Offset(I - 1, 2).Value
        End If
    Next
    xPreStr = xRegStr
    End If
    Application.ScreenUpdating = True
End Sub

Hinweise: Im Code sind „Drink“ und „Item“ die Namen der beiden Listenfelder, ändern Sie sie in Ihre eigenen Namen.

6. Drücken Sie die Tasten Alt + Q, um das Microsoft Visual Basic for Applications-Fenster zu schließen.

7. Deaktivieren Sie den Entwurfsmodus, indem Sie auf Entwickler > Entwurfsmodus klicken.

Ab sofort werden beim Auswählen einer Getränkeart wie Kaffee im übergeordneten Listenfeld alle Kaffee-Elemente im zweiten angezeigt. Beim Auswählen von Tee oder Wein werden nur die Tee- oder Wein-Elemente im zweiten Listenfeld angezeigt. Siehe Screenshot:

Verwandte Artikel:

• Wie löscht man eine abhängige Dropdown-Listen-Zelle nachdem sich die Auswahl in Excel geändert hat?
• Wie erstellt man abhängige kaskadierende Dropdown-Listen in Excel?
• Wie füllt man andere Zellen automatisch aus, wenn man Werte in einer Excel-Dropdown-Liste auswählt?
• Wie erstellt man einen Dropdown-Kalender in Excel?
• Wie speichert oder behält man die Auswahl von ActiveX-Listenfeldern in Excel bei?