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
- Auswahlliste über Daten, Datentools, Datenüberprüfung mit „Zulassen: Liste“ einrichten.
- Rechtsklick auf den Blattreiter, Code anzeigen, den Code unten einfügen.
- 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 vorher | Auswahl | Inhalt nachher |
|---|---|---|
| (leer) | Anna | Anna |
| Anna | Ben | Anna, Ben |
| Anna, Ben | Clara | Anna, Ben, Clara |
| Anna, Ben, Clara | Ben | Anna, Clara |
| Anna, Annabell | Anna | Annabell |
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:
| Eintrag | Anzahl |
|---|---|
| Anna | 2 |
| Ben | 3 |
| Benedikt | 1 |
| Clara | 2 |
| Gesamt | 8 |
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.
