Dropdown mit Mehrfachauswahl in Excel per VBA

Eine Auswahlliste in Excel übernimmt immer genau einen Eintrag. Wählen Sie einen zweiten, ersetzt er den ersten. Für eine Teilnehmerliste, Schlagworte oder Zuständigkeiten brauchen Sie oft mehrere Einträge in einer Zelle, etwa „Anna, Ben, Clara“. Mit wenigen Zeilen VBA-Code wird aus der normalen Auswahlliste eine Mehrfachauswahl.

Die Kurzlösung

  1. Auswahlliste über Daten, Datentools, Datenüberprüfung mit „Zulassen: Liste“ einrichten.
  2. Rechtsklick auf den Blattreiter, Code anzeigen, den Code unten einfügen.
  3. Im Code den Bereich der Auswahlzellen eintragen (BEREICH) und die Datei als Arbeitsmappe mit Makros (.xlsm) speichern.

Danach hängt jede Auswahl den Eintrag an den vorhandenen Inhalt an. Wählen Sie einen Eintrag, der schon in der Zelle steht, wird er wieder entfernt.

Schritt 1: Auswahlliste einrichten

Schreiben Sie die möglichen Einträge untereinander, am besten auf ein eigenes Blatt, zum Beispiel „Liste“. Markieren Sie die Einträge und formatieren Sie sie mit Strg+T als Tabelle. So wächst die Liste mit, wenn Sie später Namen ergänzen. Vergeben Sie für die Spalte im Namensfeld links neben der Bearbeitungsleiste einen Namen, etwa Teilnehmer.

Markieren Sie dann die Zellen, in denen ausgewählt werden soll, zum Beispiel B4:B14. Unter Daten, Datentools, Datenüberprüfung wählen Sie bei Zulassen „Liste“ und tragen als Quelle =Teilnehmer ein.

Auswahlliste automatisch aus vorhandenen Daten (Microsoft 365)

Oft gibt es die Einträge schon, nur mit Wiederholungen, etwa die Namen in einer Projektliste. In Excel für Microsoft 365 erzeugt eine Formel daraus die Auswahlliste, ohne Doppelte und alphabetisch sortiert. Stehen die Namen auf dem Blatt „Liste“ in Spalte A, schreiben Sie in C2:

=SORTIEREN(EINDEUTIG(A2:.A1000))

Der Punkt in A2:.A1000 kürzt den Bereich auf die gefüllten Zellen, leere Zeilen am Ende erscheinen nicht als Eintrag. In der Datenüberprüfung tragen Sie dann als Quelle =Liste!$C$2# ein. Das Zeichen # steht für den ganzen Überlaufbereich der Formel. Kommt ein neuer Name hinzu, steht er sofort in der Auswahl.

Eine leere Zelle mitten in der Liste würde als 0 in der Auswahl landen, denn der Punkt entfernt nur Leerzellen am Anfang und am Ende. Hat Ihre Liste Lücken oder kennt Ihre Excel-Version den Punkt im Bezug noch nicht, verwenden Sie =SORTIEREN(EINDEUTIG(FILTER(A2:A1000;A2:A1000<>""))).

Schritt 2: VBA-Code einfügen

Der Code reagiert auf jede Änderung im Tabellenblatt. Er gehört deshalb in das Codemodul des Blatts, nicht in ein allgemeines Modul. Klicken Sie mit der rechten Maustaste auf den Blattreiter, wählen Sie Code anzeigen und fügen Sie ein:

Option Explicit

' Mehrfachauswahl per Auswahlliste (Datenüberprüfung, Liste)
' Gehört in das Codemodul des Tabellenblatts.

Private Const BEREICH As String = "B4:B14"   ' Zellen mit Mehrfachauswahl
Private Const TRENNER As String = ", "       ' Trennzeichen zwischen den Einträgen

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim neuerWert As String
    Dim alterWert As String

    On Error GoTo Aufraeumen

    ' Nur einzelne Zellen im Bereich, die eine Auswahlliste haben
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Me.Range(BEREICH)) Is Nothing Then Exit Sub
    If Not HatAuswahlliste(Target) Then Exit Sub

    neuerWert = CStr(Target.Value)
    If neuerWert = "" Then Exit Sub           ' Entf leert die Zelle wie gewohnt

    Application.EnableEvents = False          ' verhindert, dass sich das Makro selbst auslöst
    Application.Undo                          ' holt den Inhalt vor der Auswahl zurück
    alterWert = CStr(Target.Value)
    Target.Value = Umschalten(alterWert, neuerWert)

Aufraeumen:
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox "Die Auswahl konnte nicht übernommen werden: " & Err.Description, vbExclamation
End Sub

' Hängt den Eintrag an oder entfernt ihn, wenn er schon vorhanden ist
Private Function Umschalten(ByVal alterWert As String, ByVal eintrag As String) As String
    Dim teile As Variant
    Dim i As Long
    Dim ergebnis As String
    Dim gefunden As Boolean

    teile = Split(alterWert, TRENNER)
    For i = LBound(teile) To UBound(teile)
        If teile(i) = eintrag Then
            gefunden = True
        Else
            If ergebnis <> "" Then ergebnis = ergebnis & TRENNER
            ergebnis = ergebnis & teile(i)
        End If
    Next i

    If Not gefunden Then
        If ergebnis <> "" Then ergebnis = ergebnis & TRENNER
        ergebnis = ergebnis & eintrag
    End If
    Umschalten = ergebnis
End Function

' Prüft, ob die Zelle eine Datenüberprüfung vom Typ Liste hat
Private Function HatAuswahlliste(ByVal zelle As Range) As Boolean
    Dim typ As Long
    typ = -1
    On Error Resume Next                      ' Zelle ohne Datenüberprüfung löst einen Fehler aus
    typ = zelle.Validation.Type
    On Error GoTo 0
    HatAuswahlliste = (typ = xlValidateList)
End Function

So arbeitet der Code

Wählen Sie in der Liste einen Eintrag, hat Excel den alten Inhalt der Zelle bereits überschrieben, wenn das Ereignis Worksheet_Change startet. Der Code merkt sich deshalb den neuen Eintrag, holt mit Application.Undo den alten Inhalt zurück und schreibt dann beides zusammen in die Zelle.

Die Funktion Umschalten zerlegt den bisherigen Inhalt am Trennzeichen in einzelne Einträge. Ist der gewählte Eintrag schon dabei, lässt sie ihn weg, sonst hängt sie ihn an:

Inhalt vorherAuswahlInhalt nachher
(leer)AnnaAnna
AnnaBenAnna, Ben
Anna, BenClaraAnna, Ben, Clara
Anna, Ben, ClaraBenAnna, Clara
Anna, AnnabellAnnaAnnabell

Weil die Einträge vollständig verglichen werden, entfernt die Auswahl von „Anna“ nicht versehentlich „Annabell“.

Ein anderes Trennzeichen stellen Sie in der Zeile mit TRENNER ein, zum Beispiel "; " oder " / ". Weitere Bereiche geben Sie mit Komma getrennt an: "B4:B14,D4:D14".

Mehrfachauswahl auswerten

Um zu zählen, wie oft ein Name vorkommt, liegt ZÄHLENWENN mit Platzhalter nahe:

=ZÄHLENWENN(B4:B14;"*Ben*")

Diese Formel zählt aber auch „Benedikt“ mit. Genau zählen Sie, indem Sie den Namen mit den Trennzeichen suchen:

=SUMMENPRODUKT(--ISTZAHL(SUCHEN(", Ben, ";", "&B4:B14&", ")))

In Excel für Microsoft 365 zählt eine einzige Formel, wie oft jeder Eintrag insgesamt gewählt wurde:

=LET(a;TEXTTEILEN(TEXTVERKETTEN(", ";WAHR;B4:B14);;", ");
     GRUPPIERENNACH(a;a;ANZAHL2))

TEXTVERKETTEN hängt alle Zellen zu einem langen Text zusammen, TEXTTEILEN zerlegt ihn am Trennzeichen wieder in einzelne Einträge untereinander, und GRUPPIERENNACH zählt jeden Eintrag. Mit den Zellen „Anna, Ben“, „Ben, Clara“, „Anna, Benedikt, Ben“ und „Clara“ entsteht diese Übersicht:

EintragAnzahl
Anna2
Ben3
Benedikt1
Clara2
Gesamt8

Ben wird genau gezählt, Benedikt steht getrennt daneben. Die Liste ist alphabetisch sortiert und wächst von selbst, wenn neue Namen gewählt werden. TEXTVERKETTEN verarbeitet höchstens 32.767 Zeichen, für sehr lange Listen ist das die Grenze.

Typische Fehler

  • Code im falschen Modul: In einem allgemeinen Modul (Modul1) wird Worksheet_Change nie ausgelöst. Der Code muss im Codemodul des Tabellenblatts stehen.
  • Makros deaktiviert oder Datei als .xlsx gespeichert: Beim Speichern als .xlsx entfernt Excel den Code. Speichern Sie als .xlsm und aktivieren Sie beim Öffnen die Inhalte.
  • Rückgängig funktioniert nicht mehr: Nach jeder Auswahl ist der Verlauf für Strg+Z leer. Das liegt daran, dass Makros den Rückgängig-Verlauf grundsätzlich löschen.
  • Ungültige Einträge eingekreist: Excel prüft die Datenüberprüfung nur bei der Eingabe von Hand. Der Code darf daher „Anna, Ben“ schreiben, obwohl das nicht in der Liste steht. Der Befehl „Ungültige Daten einkreisen“ markiert solche Zellen trotzdem als ungültig.
  • Trennzeichen im Eintrag: Enthält ein Listeneintrag selbst das Trennzeichen, etwa „Müller, Hans“, kann der Code ihn nicht mehr sauber erkennen. Wählen Sie dann ein anderes Trennzeichen, zum Beispiel "; ".
  • Eintrag von Hand überschreiben: Tippen Sie in eine Zelle mit Mehrfachauswahl einen Wert aus der Liste, wird er ebenfalls angehängt. Zum Neuanfang leeren Sie die Zelle mit Entf.

Mit KI schneller zum Ziel

Code für eine Mehrfachauswahl gehört zu den Aufgaben, die ChatGPT, Claude oder Copilot in Sekunden liefern. Ob er im Alltag hält, zeigt sich erst bei den Sonderfällen. Typische Schwachstellen in solchem Code: Application.EnableEvents wird nach einem Fehler nicht wieder eingeschaltet, und danach reagiert das Blatt auf keine Änderung mehr. Oder der Code entfernt mit „Anna“ auch „Annabell“, weil er nur nach Textteilen sucht. Testen Sie generierten Code deshalb mit den Fällen aus der Tabelle oben, bevor andere mit der Datei arbeiten. Warum KI-Code oft nicht auf Anhieb läuft, erklärt der Beitrag VBA-Code von ChatGPT: Warum er oft nicht funktioniert.

In unseren Projekten schreiben KI-Werkzeuge einen Teil des Codes mit, getestet und abgenommen wird er von uns. So sinkt der Aufwand, ohne dass die Qualität leidet. Mehr dazu unter KI und Automatisierung. Sind KI-erzeugte Makros bei Ihnen schon im Einsatz, prüfen wir sie im Rahmen einer Prüfung von Arbeitsmappen.

Aus der Praxis: von der Auswahlliste zur Erfassungsmaske

Eine Mehrfachauswahl ist oft der Anfang. In Projekten wird daraus eine Erfassung mit abhängigen Auswahllisten, Pflichtfeldern, Prüfregeln und einer Auswertung auf Knopfdruck, die mehrere Kollegen gleichzeitig nutzen. Damit so eine Datei auch nach Jahren noch zuverlässig läuft, kommt es auf sauberen, dokumentierten Code an. Wie wir solche Werkzeuge entwickeln, zeigt die Seite Excel-Programmierung.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.