Mehrfachauswahl in Excel-Dropdown-Listen – vollständige Anleitung

Auswahllisten in Excel sind ein fantastisches Werkzeug, um Datenkonsistenz und einfache Eingabe zu gewährleisten. Standardmäßig erlauben sie jedoch nur die Auswahl eines einzelnen Elements. Was aber, wenn Sie mehrere Elemente aus derselben Auswahlliste auswählen müssen? Dieser umfassende Leitfaden untersucht Methoden, um Mehrfachauswahl in Excel-Auswahllisten zu ermöglichen, Duplikate zu verwalten, benutzerdefinierte Trennzeichen festzulegen und den Geltungsbereich dieser Listen zu definieren.

• Verwendung von VBA-Code
• Verwendung von Kutools für Excel mit wenigen Klicks

• Duplikate zulassen
• Vorhandene Elemente entfernen
• Benutzerdefiniertes Trennzeichen festlegen
• Angegebenen Bereich festlegen
• In einem geschützten Arbeitsblatt ausführen

Mehrfachauswahl in Auswahllisten aktivieren

Dieser Abschnitt bietet zwei Methoden, um Ihnen bei der Aktivierung der Mehrfachauswahl in Auswahllisten in Excel zu helfen.

Verwendung von VBA-Code

Um Mehrfachauswahl in einer Auswahlliste zu ermöglichen, können Sie "Visual Basic for Applications" (VBA) in Excel verwenden. Das Skript kann das Verhalten einer Auswahlliste so ändern, dass es eine Mehrfachauswahl-Liste wird. Gehen Sie wie folgt vor.

Schritt 1: Öffnen des Blatt-Editors (Code)

1. Öffnen Sie das Arbeitsblatt, das die Auswahlliste enthält, für die Sie Mehrfachauswahl aktivieren möchten.
2. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie "Code anzeigen" aus dem Kontextmenü.
   

Schritt 2: Verwenden von VBA-Code

Kopieren Sie nun den folgenden VBA-Code und fügen Sie ihn in das geöffnete Blattfenster (Code) ein.

VBA-Code: Mehrfachauswahl in Excel-Auswahllisten aktivieren.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Ergebnis

Wenn Sie zum Arbeitsblatt zurückkehren, ermöglicht die Auswahlliste die Auswahl mehrerer Optionen, siehe Demo unten:

Verwendung von Kutools für Excel mit wenigen Klicks

Wenn Sie sich mit VBA nicht wohl fühlen, bietet sich eine einfachere Alternative: die Funktion "Dropdown-Liste mit Mehrfachauswahl" von "Kutools für Excel". Dieses benutzerfreundliche Tool vereinfacht die Aktivierung der Mehrfachauswahl in Auswahllisten und ermöglicht es Ihnen, das Trennzeichen anzupassen und Duplikate mühelos zu verwalten, um unterschiedliche Bedürfnisse zu erfüllen.

Nach der Installation von Kutools für Excel gehen Sie zur Registerkarte "Kutools", wählen Sie "Dropdown-Liste" > "Dropdown-Liste mit Mehrfachauswahl". Dann müssen Sie wie folgt konfigurieren.

1. Legen Sie den Bereich fest, der die Dropdown-Liste enthält, aus der Sie mehrere Elemente auswählen müssen.
2. Legen Sie das Trennzeichen für die ausgewählten Elemente in der Zelle der Dropdown-Liste fest.
3. Klicken Sie auf "OK", um die Einstellungen abzuschließen.
   

Ergebnis

Nun, wenn Sie auf eine Zelle mit einer Dropdown-Liste im angegebenen Bereich klicken, erscheint daneben eine Listenbox. Klicken Sie einfach auf die "+"-Schaltfläche neben den Elementen, um sie zur Dropdown-Zelle hinzuzufügen, und klicken Sie auf die "-"-Schaltfläche, um Elemente zu entfernen, die Sie nicht mehr benötigen. Siehe Demo unten:

Weitere Operationen für Mehrfachauswahl-Dropdown-Listen

Dieser Abschnitt sammelt verschiedene Szenarien, die möglicherweise erforderlich sind, wenn Mehrfachauswahl in der Datenvalidierungs-Dropdown-Liste aktiviert wird.

Duplikate in der Dropdown-Liste zulassen

Duplikate können ein Problem sein, wenn Mehrfachauswahl in einer Dropdown-Liste erlaubt ist. Der oben genannte VBA-Code erlaubt keine Duplikate in der Dropdown-Liste. Wenn Sie Duplikate behalten müssen, probieren Sie den VBA-Code in diesem Abschnitt.

VBA-Code: Duplikate in der Datenvalidierungs-Dropdown-Liste zulassen

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        Target.Value = xValue1 & delimiter & xValue2
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Ergebnis

Jetzt können Sie mehrere Elemente aus den Dropdown-Listen im aktuellen Arbeitsblatt auswählen. Um ein Element in einer Dropdown-Listen-Zelle zu wiederholen, wählen Sie dieses Element weiterhin aus der Liste aus. Siehe Screenshot:

Entfernen vorhandener Elemente aus der Dropdown-Liste

Nachdem Sie mehrere Elemente aus einer Dropdown-Liste ausgewählt haben, möchten Sie möglicherweise ein vorhandenes Element aus der Dropdown-Listen-Zelle entfernen. Dieser Abschnitt bietet ein weiteres VBA-Skript, um Ihnen dabei zu helfen.

VBA-Code: Entfernen vorhandener Elemente aus der Dropdown-Listen-Zelle

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRngDV As Range
    Dim TargetRange As Range
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String
    Dim allValues As Variant
    Dim valueExists As Boolean
    Dim i As Long
    Dim cleanedValue As String

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Set your desired delimiter here

    If Target.CountLarge > 1 Then Exit Sub

    ' Check if the change is within the specific range
    If Intersect(Target, TargetRange) Is Nothing Then Exit Sub

    On Error Resume Next
    Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If xRngDV Is Nothing Or Target.Value = "" Then
        ' Skip if there's no data validation or if the cell is cleared
        Application.EnableEvents = True
        Exit Sub
    End If
    On Error GoTo 0

    If Not Intersect(Target, xRngDV) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue

        ' Split the old value by delimiter and check if new value already exists
        allValues = Split(oldValue, delimiter)
        valueExists = False
        For i = LBound(allValues) To UBound(allValues)
            If Trim(allValues(i)) = newValue Then
                valueExists = True
                Exit For
            End If
        Next i

        ' Add or remove value based on its existence
        If valueExists Then
            ' Remove the value
            cleanedValue = ""
            For i = LBound(allValues) To UBound(allValues)
                If Trim(allValues(i)) <> newValue Then
                    If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
                    cleanedValue = cleanedValue & Trim(allValues(i))
                End If
            Next i
            Target.Value = cleanedValue
        Else
            ' Add the value
            If oldValue <> "" Then
                Target.Value = oldValue & delimiter & newValue
            Else
                Target.Value = newValue
            End If
        End If

        Application.EnableEvents = True
    End If
End Sub

Ergebnis

Dieser VBA-Code ermöglicht es Ihnen, mehrere Elemente aus einer Dropdown-Liste auszuwählen und leicht jedes bereits gewählte Element zu entfernen. Nachdem Sie mehrere Elemente ausgewählt haben, können Sie ein bestimmtes Element einfach durch erneutes Auswählen aus der Liste entfernen.

Festlegen eines benutzerdefinierten Trennzeichens

Das Trennzeichen ist in den obigen VBA-Codes als Komma eingestellt. Sie können diese Variable in ein beliebiges gewünschtes Zeichen ändern, um es als Trennzeichen für die Dropdown-Listen-Auswahl zu verwenden. So geht's:

Wie Sie sehen können, enthalten die obigen VBA-Codes alle die folgende Zeile:

delimiter = ", "

Sie müssen nur das Komma in ein beliebiges Trennzeichen ändern, das Sie benötigen. Wenn Sie beispielsweise die Elemente durch Semikolon trennen möchten, ändern Sie die Zeile in:

delimiter = "; "

delimiter = vbNewLine

Festlegen eines angegebenen Bereichs

Die obigen VBA-Codes gelten für alle Dropdown-Listen im aktuellen Arbeitsblatt. Wenn Sie möchten, dass die VBA-Codes nur für einen bestimmten Bereich von Dropdown-Listen gelten, können Sie den Bereich in dem obigen VBA-Code wie folgt festlegen.

Wie Sie sehen können, enthalten die obigen VBA-Codes alle die folgende Zeile:

Set TargetRange = Me.UsedRange

Sie müssen die Zeile nur in Folgendes ändern:

Set TargetRange = Me.Range("C2:C10")

Ausführen in einem geschützten Arbeitsblatt

Stellen Sie sich vor, Sie haben ein Arbeitsblatt mit dem Passwort "123" geschützt und die Dropdown-Listen-Zellen vor der Aktivierung des Schutzes auf "Entsperrt" gesetzt, um sicherzustellen, dass die Mehrfachauswahlfunktion nach dem Schutz aktiv bleibt. Die oben genannten VBA-Codes funktionieren jedoch in diesem Fall nicht, und dieser Abschnitt beschreibt ein weiteres VBA-Skript, das speziell für die Handhabung der Mehrfachauswahlfunktion in einem geschützten Arbeitsblatt entwickelt wurde.

VBA-Code: Mehrfachauswahl in Dropdown-Liste ohne Duplikate aktivieren

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range
    Dim isProtected As Boolean
    Dim pswd As Variant

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    
    ' Check if sheet is protected
    isProtected = Me.ProtectContents
    If isProtected Then
        ' If protected, temporarily unprotect. Adjust or remove the password as needed.
        pswd = "yourPassword" ' Change or remove this as needed
        Me.Unprotect Password:=pswd
    End If

    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        If isProtected Then Me.Protect Password:=pswd
        Exit Sub
    End If
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0

    ' Re-protect the sheet if it was protected
    If isProtected Then
        Me.Protect Password:=pswd
    End If
End Sub

Durch die Aktivierung der Mehrfachauswahl in Excel-Dropdown-Listen können Sie die Funktionalität und Flexibilität Ihrer Arbeitsblätter erheblich verbessern. Ob Sie sich mit VBA-Codierung auskennen oder eine einfachere Lösung wie Kutools bevorzugen, Sie haben jetzt die Möglichkeit, Ihre Standard-Dropdown-Listen in dynamische, Mehrfachauswahl-Werkzeuge zu verwandeln. Mit diesen Fähigkeiten sind Sie nun in der Lage, dynamischere und benutzerfreundlichere Excel-Dokumente zu erstellen. Für diejenigen, die tiefer in die Möglichkeiten von Excel eintauchen möchten, bietet unsere Website eine Vielzahl von Tutorials. Entdecken Sie hier weitere Excel-Tipps und -Tricks.

Verwandte Artikel