Excel-Dropdown-Listen sind ein fantastisches Tool zur Gewährleistung der Datenkonsistenz und einer einfachen Eingabe. Allerdings beschränken sie Sie standardmäßig auf die Auswahl nur eines Elements. Was aber, wenn Sie mehrere Elemente aus derselben Dropdown-Liste auswählen müssen? In diesem umfassenden Leitfaden werden Methoden zum Aktivieren von Mehrfachauswahlen in Excel-Dropdown-Listen, zum Verwalten von Duplikaten, zum Festlegen benutzerdefinierter Trennzeichen und zum Definieren des Umfangs dieser Listen erläutert.

• Mit VBA-Code
• Verwenden Sie Kutools für Excel mit wenigen Klicks

• Duplikate von Elementen zulassen
• Entfernen aller vorhandenen Elemente
• Festlegen eines benutzerdefinierten Trennzeichens
• Festlegen eines bestimmten Bereichs
• Ausführung in einem geschützten Arbeitsblatt

Aktivieren mehrerer Auswahlmöglichkeiten in der Dropdown-Liste

In diesem Abschnitt werden zwei Methoden beschrieben, die Ihnen dabei helfen, Mehrfachauswahlen in der Dropdown-Liste in Excel zu aktivieren.

Verwenden von VBA-Code

Um eine Mehrfachauswahl in der Dropdown-Liste zu ermöglichen, können Sie verwenden Visual Basic für Applikationen (VBA) in Excel. Das Skript kann das Verhalten einer Dropdown-Liste ändern, um daraus eine Multiple-Choice-Liste zu machen. Bitte gehen Sie wie folgt vor.

Schritt 1: Öffnen Sie den Blatt-(Code-)Editor

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

Schritt 2: Verwenden Sie VBA-Code

Kopieren Sie nun den folgenden VBA-Code und fügen Sie ihn in das Eröffnungsblattfenster (Codefenster) ein.

VBA-Code: Aktivieren Sie die Mehrfachauswahl in der Excel-Dropdown-Liste.

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, können Sie in der Dropdown-Liste mehrere Optionen auswählen. Sehen Sie sich die Demo unten an:

Verwenden Sie Kutools für Excel mit wenigen Klicks

Wenn Sie mit VBA nicht vertraut sind, gibt es eine einfachere Alternative Kutools for Excel's Mehrfachauswahl-Dropdown-Liste Besonderheit. Dieses benutzerfreundliche Tool vereinfacht die Aktivierung mehrerer Auswahlmöglichkeiten in Dropdown-Listen, sodass Sie das Trennzeichen anpassen und Duplikate mühelos verwalten können, um Ihren unterschiedlichen Anforderungen gerecht zu werden.

Nach der Kutools für Excel installieren, Auf dem Sprung Kutoolen Registerkarte, wählen Sie Dropdown-Liste > Mehrfachauswahl-Dropdown-Liste. Dann müssen Sie wie folgt konfigurieren.

1. Geben Sie den Bereich an, der die Dropdown-Liste enthält, aus der Sie mehrere Elemente auswählen müssen.
2. Geben Sie das Trennzeichen für die ausgewählten Elemente in der Dropdown-Listenzelle an.
3. Klicken Sie OK um die Einstellungen zu vervollständigen.
   

Ergebnis

Wenn Sie nun auf eine Zelle mit einer Dropdown-Liste im angegebenen Bereich klicken, wird daneben ein Listenfeld angezeigt. 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 alle Elemente zu entfernen, die Sie nicht mehr benötigen. Sehen Sie sich die Demo unten an:

Weitere Operationen für die Dropdown-Liste mit Mehrfachauswahl

In diesem Abschnitt werden die verschiedenen Szenarien erfasst, die erforderlich sein können, wenn in der Dropdown-Liste „Datenvalidierung“ mehrere Auswahlmöglichkeiten aktiviert werden.

Duplikate von Elementen in der Dropdown-Liste zulassen

Duplikate können ein Problem darstellen, wenn in einer Dropdown-Liste Mehrfachauswahlen zulässig sind. Der obige VBA-Code lässt keine doppelten Elemente in der Dropdown-Liste zu. Wenn Sie doppelte Elemente behalten müssen, probieren Sie den VBA-Code in diesem Abschnitt aus.

VBA-Code: Duplikate in der Dropdown-Liste zur Datenvalidierung 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-Listenzelle zu wiederholen, wählen Sie dieses Element weiterhin aus der Liste aus. Siehe Screenshot:

Entfernen aller vorhandenen Elemente aus der Dropdown-Liste

Nachdem Sie mehrere Elemente aus einer Dropdown-Liste ausgewählt haben, müssen Sie möglicherweise manchmal ein vorhandenes Element aus der Dropdown-Listenzelle entfernen. In diesem Abschnitt finden Sie einen weiteren Teil des VBA-Codes, der Sie bei der Bewältigung dieser Aufgabe unterstützt.

VBA-Code: Entfernen Sie alle vorhandenen Elemente aus der Dropdown-Listenzelle

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

Mit diesem VBA-Code können Sie mehrere Elemente aus einer Dropdown-Liste auswählen und bereits ausgewählte Elemente problemlos entfernen. Wenn Sie nach der Auswahl mehrerer Elemente ein bestimmtes Element entfernen möchten, wählen Sie es einfach erneut aus der Liste aus.

Festlegen eines benutzerdefinierten Trennzeichens

Das Trennzeichen wird in den oben genannten VBA-Codes als Komma festgelegt. Sie können diese Variable in ein beliebiges Zeichen ändern, das als Trennzeichen für die Dropdown-Listenauswahl verwendet werden soll. So können Sie vorgehen:

Wie Sie sehen können, haben die oben genannten VBA-Codes alle die folgende Zeile:

delimiter = ", "

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

delimiter = "; "

delimiter = vbNewLine

Festlegen eines bestimmten Bereichs

Die oben genannten 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 im obigen VBA-Code wie folgt angeben.

Wie Sie sehen können, haben die oben genannten VBA-Codes alle die folgende Zeile:

Set TargetRange = Me.UsedRange

Sie müssen nur die Zeile ändern in:

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

Ausführung in einem geschützten Arbeitsblatt

Stellen Sie sich vor, Sie hätten ein Arbeitsblatt mit dem Passwort geschützt „123" und setzen Sie die Dropdown-Listenzellen auf "Entriegelt" vor der Aktivierung des Schutzes, um sicherzustellen, dass die Mehrfachauswahlfunktion nach dem Schutz aktiv bleibt. Die oben genannten VBA-Codes können in diesem Fall jedoch nicht funktionieren, und in diesem Abschnitt wird ein weiteres VBA-Skript beschrieben, das speziell für die Handhabung der Mehrfachauswahlfunktion entwickelt wurde in einem geschützten Arbeitsblatt.

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 mehrerer Auswahlmöglichkeiten in Excel-Dropdown-Listen können Sie die Funktionalität und Flexibilität Ihrer Arbeitsblätter erheblich verbessern. Ganz gleich, ob Sie mit der VBA-Codierung vertraut sind oder eine einfachere Lösung wie Kutools bevorzugen, Sie haben jetzt die Möglichkeit, Ihre Standard-Dropdown-Listen in dynamische Mehrfachauswahl-Tools umzuwandeln. Mit diesen Fähigkeiten sind Sie nun in der Lage, dynamischere und benutzerfreundlichere Excel-Dokumente zu erstellen. Für diejenigen, die tiefer in die Funktionen von Excel eintauchen möchten, bietet unsere Website eine Fülle von Tutorials. Entdecken Sie hier weitere Excel-Tipps und Tricks.

Ähnliche Artikel