Selecteer meerdere items in Excel-keuzelijst – volledige handleiding

Keuzelijsten in Excel zijn een fantastisch hulpmiddel om gegevensconsistentie en invoer gemakkelijk te maken. Standaard beperken ze je echter tot het selecteren van slechts één item. Maar wat als je meerdere items uit dezelfde keuzelijst moet selecteren? Deze uitgebreide handleiding onderzoekt methoden om meervoudige selecties mogelijk te maken in Excel-keuzelijsten, duplicaten te beheren, aangepaste scheidingstekens in te stellen en de scope van deze lijsten te definiëren.

• Met behulp van VBA-code
• Kutools voor Excel in een paar klikken gebruiken

• Dubbele items toestaan
• Bestaande items verwijderen
• Een aangepast scheidingsteken instellen
• Een specifiek bereik instellen
• Uitvoeren in een beveiligd werkblad

Meerdere selecties inschakelen in keuzelijst

Deze sectie biedt twee methoden om u te helpen meerdere selecties mogelijk te maken in een keuzelijst in Excel.

Met behulp van VBA-code

Om meerdere selecties in een keuzelijst toe te staan, kunt u 'Visual Basic for Applications' (VBA) in Excel gebruiken. Het script kan het gedrag van een keuzelijst wijzigen om er een meerkeuze-lijst van te maken. Volg de volgende stappen.

Stap 1: Open de Sheet (Code) editor

1. Open het werkblad dat de keuzelijst bevat waarvoor u meerdere selecties wilt inschakelen.
2. Klik met de rechtermuisknop op het tabblad van het werkblad en selecteer "Bekijk code" in het contextmenu.
   

Stap 2: Gebruik VBA-code

Kopieer nu de volgende VBA-code en plak deze in het venster van het opene werkblad (Code).

VBA-code: Schakel meerdere selecties in Excel-keuzelijst in.

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

Resultaat

Wanneer u terugkeert naar het werkblad, zal de keuzelijst u in staat stellen meerdere opties te kiezen, zie de demo hieronder:

Kutools voor Excel in een paar klikken gebruiken

Als u niet op uw gemak bent met VBA, is een eenvoudiger alternatief de functie 'Multi-select Keuzelijst' van 'Kutools voor Excel'. Dit gebruiksvriendelijke hulpmiddel vereenvoudigt het inschakelen van meerdere selecties in keuzelijsten, waardoor u het scheidingsteken eenvoudig kunt aanpassen en duplicaten moeiteloos kunt beheren om aan uw verschillende behoeften te voldoen.

Na het installeren van Kutools voor Excel, ga naar het tabblad 'Kutools', selecteer 'Keuzelijst' > 'Multi-select Keuzelijst'. Dan moet u als volgt configureren.

1. Specificeer het bereik dat de keuzelijst bevat waaruit u meerdere items moet selecteren.
2. Specificeer het scheidingsteken voor de geselecteerde items in de cel van de keuzelijst.
3. Klik op 'OK' om de instellingen te voltooien.
   

Resultaat

Nu, wanneer u op een cel met een keuzelijst in het gespecificeerde bereik klikt, verschijnt er een lijstvak naast het. Klik gewoon op de '+' knop naast de items om ze toe te voegen aan de keuzelijstcel, en klik op de '-' knop om items die u niet meer wilt te verwijderen. Zie de demo hieronder:

Meer bewerkingen voor meervoudige selectie keuzelijst

Deze sectie verzamelt de verschillende scenario's die nodig kunnen zijn bij het inschakelen van meerdere selecties in de Validatiekeuzelijst.

Dubbele items toestaan in keuzelijst

Dubbele items kunnen een probleem zijn wanneer meerdere selecties zijn toegestaan in een keuzelijst. De bovenstaande VBA-code staat geen dubbele items toe in de keuzelijst. Als u dubbele items wilt behouden, probeer dan de VBA-code in deze sectie.

VBA-code: Dubbele items toestaan in validatiekeuzelijst

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

Resultaat

Nu kunt u meerdere items selecteren uit de keuzelijsten in het huidige werkblad. Om een item in een keuzelijstcel te herhalen, blijf dat item selecteren uit de lijst. Zie screenshot:

Bestaande items verwijderen uit keuzelijst

Nadat u meerdere items uit een keuzelijst hebt geselecteerd, moet u soms een bestaand item uit de keuzelijstcel verwijderen. Deze sectie biedt een ander stukje VBA-code om u hierbij te helpen.

VBA-code: Verwijder bestaande items uit de keuzelijstcel

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

Resultaat

Deze VBA-code stelt u in staat meerdere items uit een keuzelijst te selecteren en eenvoudig elk item dat u al gekozen hebt te verwijderen. Nadat u meerdere items hebt geselecteerd, kunt u een specifiek item verwijderen door het opnieuw uit de lijst te selecteren.

Een aangepast scheidingsteken instellen

Het scheidingsteken is ingesteld als komma in de bovenstaande VBA-codes. U kunt deze variabele wijzigen naar elk gewenst teken om te gebruiken als scheidingsteken voor de keuzelijstselecties. Hier is hoe u dat kunt doen:

Zoals u kunt zien, hebben de bovenstaande VBA-codes allemaal de volgende regel:

delimiter = ", "

U hoeft alleen de komma te veranderen in elk scheidingsteken dat u nodig hebt. Bijvoorbeeld, als u de items wilt scheiden met een puntkomma, verander de regel naar:

delimiter = "; "

delimiter = vbNewLine

Een specifiek bereik instellen

De bovenstaande VBA-codes zijn van toepassing op alle keuzelijsten in het huidige werkblad. Als u wilt dat de VBA-codes alleen van toepassing zijn op een bepaald bereik van keuzelijsten, kunt u het bereik in de bovenstaande VBA-code als volgt specificeren.

Zoals u kunt zien, hebben de bovenstaande VBA-codes allemaal de volgende regel:

Set TargetRange = Me.UsedRange

U hoeft alleen de regel te veranderen naar:

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

Uitvoeren in een beveiligd werkblad

Stel dat u een werkblad hebt beveiligd met het wachtwoord '123' en de cellen van de keuzelijst op 'Ontgrendeld' hebt ingesteld voordat u de beveiliging activeerde, waardoor wordt gegarandeerd dat de multi-select-functie actief blijft na de beveiliging. Echter, de bovenstaande VBA-codes werken niet in dit geval, en deze sectie beschrijft een ander VBA-script dat speciaal is ontworpen om multi-select-functionaliteit in een beveiligd werkblad te hanteren.

VBA-code: Schakel meerdere selecties in keuzelijst in zonder duplicaten

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

Door meerdere selecties in Excel-keuzelijsten in te schakelen, kunt u de functionaliteit en flexibiliteit van uw werkbladen aanzienlijk verbeteren. Of u nu op uw gemak bent met VBA-codering of een eenvoudiger oplossing zoals Kutools prefereert, u hebt nu de mogelijkheid om uw standaard keuzelijsten om te zetten in dynamische, multi-select tools. Met deze vaardigheden bent u nu uitgerust om dynamischere en gebruiksvriendelijkere Excel-documenten te maken. Voor wie graag dieper in de mogelijkheden van Excel wil duiken, biedt onze website een schat aan tutorials. Ontdek hier meer Excel-tips en -trucs.

Gerelateerde artikelen