Selecteer meerdere items in Excel-Keuzelijst – volledige handleiding

Excel-keuzelijsten zijn een fantastisch hulpmiddel om gegevensconsistentie te waarborgen en invoer eenvoudig te maken. Standaard kunt u echter slechts één item selecteren. Maar wat als u meerdere items moet kiezen uit dezelfde keuzelijst? Deze uitgebreide handleiding behandelt methoden om meervoudige selecties in Excel-keuzelijsten mogelijk te maken, duplicaten te beheren, aangepaste scheidingstekens in te stellen en het bereik van deze lijsten te definiëren.

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

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

Meervoudige selecties inschakelen in Keuzelijst

In dit gedeelte worden twee methoden beschreven waarmee u meervoudige selecties kunt inschakelen in een keuzelijst in Excel.

Met VBA-code

Om meervoudige selecties in een keuzelijst mogelijk te maken, kunt u gebruikmaken van Visual Basic for Applications (VBA) in Excel. Met een eenvoudig script past u het gedrag van de keuzelijst aan, zodat gebruikers meerdere opties tegelijk kunnen selecteren. Volg hiervoor de onderstaande stappen.

Stap 1: Open de editor Blad (Code)

1. Open het werkblad met de keuzelijst waarvoor u meervoudige selectie wilt inschakelen.
2. Klik met de rechtermuisknop op het bladtabblad en kies ‘Code weergeven’ in het contextmenu.
   

Stap 2: Gebruik VBA-code

Kopieer nu de volgende VBA-code en plak deze in het geopende venster Blad (Code).

VBA-code: Schakel meervoudige selecties in in de Excel-keuzelijst.

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, kunt u met de Keuzelijst meerdere opties kiezen; zie de demo hieronder:

Met Kutools voor Excel in een paar klikken

Als u niet bekend bent met VBA, biedt de functie „Keuzelijst met meerdere selecties maken” van Kutools voor Excel een eenvoudig alternatief. Met deze gebruiksvriendelijke tool stelt u in één handomdraai meervoudige selecties in keuzelijsten in, past u het scheidingsteken naar wens aan en beheert u duplicaten moeiteloos – precies zoals uw behoeften dat vereisen.

Na installatie van Kutools voor Excel gaat u naar het tabblad „Kutools”, kiest u „Keuzelijst” > „Keuzelijst met meerdere selecties maken” en configureert u deze als volgt.

1. Geef het bereik op dat de keuzelijst bevat waaruit u meerdere items wilt selecteren.
2. Geef het scheidingsteken op voor het aantal geselecteerde items in de keuzelijstcel.
3. Klik op „OK” om de instellingen af te ronden.
   

Resultaat

Wanneer u nu op een cel met een keuzelijst in het beperkte bereik klikt, verschijnt er direct een handige keuzelijst naast de cel. Klik eenvoudig op de „+”-knop naast een item om het toe te voegen aan de vervolgkeuzecel, of klik op de „–”-knop om items te verwijderen die u niet langer wilt behouden. Bekijk de demo hieronder:

Meer bewerkingen voor meervoudige selectie-Keuzelijst

Dit gedeelte bevat diverse scenario’s die mogelijk nodig zijn bij het inschakelen van meervoudige selecties in de gegevensvalidatie-keuzelijst.

Dubbele items toestaan in Keuzelijst

Dubbele items kunnen een probleem vormen wanneer meervoudige selecties zijn toegestaan in een keuzelijst. De bovenstaande VBA-code voorkomt dubbele items in de keuzelijst. Als u dubbele items juist wel wilt toestaan, gebruikt u de VBA-code in dit gedeelte.

VBA-code: Dubbele items toestaan in gegevensvalidatie-Keuzelijst

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

U kunt nu meerdere items selecteren uit de keuzelijst in het huidige werkblad. Om een item herhaaldelijk te selecteren in een keuzelijst-cel, kiest u dat item gewoon opnieuw uit de lijst. Zie screenshot:

Bestaande items verwijderen uit Keuzelijst

Nadat u meerdere items hebt geselecteerd in een keuzelijst, wilt u soms een bestaand item weer verwijderen uit de keuzelijstcel. In dit gedeelte vindt u een handig stukje VBA-code om dat eenvoudig te doen.

VBA-code: Bestaande items verwijderen uit de Keuzelijst-cel

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

Met deze VBA-code selecteert u eenvoudig meerdere items uit een keuzelijst en verwijdert u elk reeds gekozen item zonder gedoe. Nadat u meerdere items hebt geselecteerd, verwijdert u een specifiek item door het opnieuw aan te klikken in de lijst.

Een aangepast scheidingsteken instellen

Het scheidingsteken is in de bovenstaande VBA-code ingesteld als komma. U kunt deze variabele eenvoudig aanpassen naar elk gewenst teken dat u als scheidingsteken voor de Keuzelijst-selecties wilt gebruiken. Zo gaat u te werk:

Zoals u kunt zien, bevatten alle bovenstaande VBA-codes de volgende regel:

delimiter = ", "

Vervang de komma eenvoudigweg door het gewenste scheidingsteken. Wilt u de items bijvoorbeeld scheiden met een puntkomma, dan wijzigt u de regel als volgt:

delimiter = "; "

delimiter = vbNewLine

Een Beperkt bereik instellen

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

Zoals u kunt zien, bevatten alle bovenstaande VBA-codes de volgende regel:

Set TargetRange = Me.UsedRange

U hoeft alleen de regel te wijzigen in:

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

Uitvoeren in een beveiligd werkblad

Stel dat u een werkblad hebt beveiligd met het wachtwoord „123” en de keuzelijstcellen voorafgaand aan het activeren van de beveiliging hebt ingesteld op „Niet vergrendeld”, zodat de multi-select-functie na beveiliging blijft werken. In dat geval functioneren de eerder genoemde VBA-codes helaas niet. Dit gedeelte beschrijft daarom een speciaal VBA-script dat is ontworpen voor multi-select-functionaliteit in een beveiligd werkblad.

VBA-code: Meervoudige selectie inschakelen in Keuzelijst 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 meervoudige selecties in een Excel-keuzelijst mogelijk te maken, verhoogt u de functionaliteit en flexibiliteit van uw werkbladen aanzienlijk. Of u nu vertrouwd bent met VBA-programmeren of liever een eenvoudigere oplossing zoals Kutools gebruikt: u kunt standaard keuzelijsten nu omzetten in dynamische hulpmiddelen met meervoudige selectie. Met deze vaardigheden creëert u nog dynamischere en gebruiksvriendelijkere Excel-documenten. Wilt u zich verder verdiepen in de mogelijkheden van Excel? Op onze website vindt u een schat aan handleidingen.Ontdek hier meer Excel-tips en -trucs.

Gerelateerde artikelen