Listele derulante Excel sunt un instrument fantastic pentru a asigura coerența datelor și ușurința introducerii. Cu toate acestea, în mod implicit, vă limitează la selectarea unui singur articol. Dar ce se întâmplă dacă trebuie să selectați mai multe articole din aceeași listă derulantă? Acest ghid cuprinzător va explora metode de activare a selecțiilor multiple în listele derulante Excel, de a gestiona duplicatele, de a seta separatoare personalizate și de a defini domeniul de aplicare al acestor liste.

• Folosind codul VBA
• Folosind Kutools pentru Excel în câteva clicuri

• Permite articole duplicate
• Eliminarea oricăror elemente existente
• Setarea unui separator personalizat
• Setarea unui interval specificat
• Executarea într-o foaie de lucru protejată

Activarea selecțiilor multiple în lista drop-down

Această secțiune oferă două metode pentru a vă ajuta să activați selecțiile multiple în lista derulantă în Excel.

Folosind codul VBA

Pentru a permite selecții multiple în lista derulantă, puteți utiliza Visual Basic pentru aplicații (VBA) în Excel. Scriptul poate modifica comportamentul unei liste derulante pentru a face din aceasta o listă cu alegeri multiple. Vă rugăm să faceți după cum urmează.

Pasul 1: Deschideți editorul Sheet (Code).

1. Deschideți foaia de lucru care conține lista derulantă pentru care doriți să activați selecția multiplă.
2. Faceți clic dreapta pe fila foii și selectați Afișați codul din meniul contextual.
   

Pasul 2: Folosiți codul VBA

Acum copiați următorul cod VBA și inserați-l în fereastra de deschidere a foii (Cod).

Cod VBA: Activați selecțiile multiple în lista derulantă Excel.

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

Rezultat

Când reveniți la foaia de lucru, lista derulantă vă va permite să alegeți mai multe opțiuni, vedeți demonstrația de mai jos:

Folosind Kutools pentru Excel în câteva clicuri

Dacă nu vă simțiți confortabil cu VBA, o alternativă mai ușoară este Kutools pentru Excel's Listă derulantă cu selectare multiplă caracteristică. Acest instrument ușor de utilizat simplifică activarea selecțiilor multiple în listele derulante, permițându-vă să personalizați separatorul și să gestionați duplicatele fără efort pentru a vă satisface diferitele nevoi.

Dupa instalarea Kutools pentru Excel, mergeți la Kutools , selectați Lista verticală > Listă derulantă cu selectare multiplă. Apoi, trebuie să configurați după cum urmează.

1. Specificați intervalul care conține lista derulantă din care trebuie să selectați mai multe elemente.
2. Specificați separatorul pentru elementele selectate în celula listei derulante.
3. Clic OK pentru a finaliza setările.
   

Rezultat

Acum, când faceți clic pe o celulă cu o listă derulantă în intervalul specificat, lângă ea va apărea o casetă de listă. Pur și simplu faceți clic pe butonul „+” de lângă elemente pentru a le adăuga în celula derulantă și faceți clic pe butonul „-” pentru a elimina elementele pe care nu le mai doriți. Vezi demo-ul de mai jos:

Mai multe operațiuni pentru lista derulantă cu selecție multiplă

Această secțiune colectează diferitele scenarii care pot fi necesare la activarea selecțiilor multiple din lista derulantă Validare date.

Permiterea elementelor duplicate în lista derulantă

Duplicatele pot fi o problemă atunci când sunt permise selecții multiple într-o listă derulantă. Codul VBA de mai sus nu permite elemente duplicate în lista derulantă. Dacă trebuie să păstrați articole duplicate, încercați codul VBA din această secțiune.

Cod VBA: Permite duplicate în lista derulantă de validare a datelor

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

Rezultat

Acum puteți selecta mai multe articole din listele derulante din foaia de lucru curentă. Pentru a repeta un element dintr-o celulă din listă derulantă, continuați să selectați acel element din listă. Vedeți captura de ecran:

Eliminarea oricăror elemente existente din lista derulantă

După ce selectați mai multe articole dintr-o listă derulantă, uneori poate fi necesar să eliminați un element existent din celula listei derulante. Această secțiune oferă o altă bucată de cod VBA pentru a vă ajuta să îndepliniți această sarcină.

Cod VBA: eliminați toate elementele existente din celula listei derulante

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

Rezultat

Acest cod VBA vă permite să selectați mai multe articole dintr-o listă derulantă și să eliminați cu ușurință orice element pe care l-ați ales deja. După ce ați selectat mai multe elemente, dacă doriți să eliminați unul anume, pur și simplu selectați-l din nou din listă.

Setarea unui separator personalizat

Delimitatorul este setat ca virgulă în codurile VBA de mai sus. Puteți modifica această variabilă la orice caracter preferat pentru a fi folosit ca separator pentru selecțiile listei derulante. Iată cum poți face:

După cum puteți vedea, toate codurile VBA de mai sus au următoarea linie:

delimiter = ", "

Trebuie doar să schimbați virgula în orice separator după cum aveți nevoie. De exemplu, doriți să separați elementele prin punct și virgulă, schimbați linia în:

delimiter = "; "

delimiter = vbNewLine

Setarea unui interval specificat

Codurile VBA de mai sus se aplică tuturor listelor derulante din foaia de lucru curentă. Dacă doriți ca codurile VBA să se aplice doar unui anumit interval de liste derulante, puteți specifica intervalul în codul VBA de mai sus, după cum urmează.

După cum puteți vedea, toate codurile VBA de mai sus au următoarea linie:

Set TargetRange = Me.UsedRange

Trebuie doar să schimbați linia în:

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

Executarea într-o foaie de lucru protejată

Imaginează-ți că ai protejat o foaie de lucru cu parola "123" și setați celulele listei derulante la "descuiat" înainte de a activa protecția, asigurându-se astfel că funcția de selecție multiplă rămâne activă după protecție. Cu toate acestea, codurile VBA menționate mai sus nu pot funcționa în acest caz, iar această secțiune descrie un alt script VBA care este special conceput pentru a gestiona funcționalitatea de selecție multiplă într-o foaie de lucru protejată.

Cod VBA: Activați selecția multiplă în lista derulantă fără duplicate

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

Prin activarea selecțiilor multiple în listele derulante Excel, puteți îmbunătăți foarte mult funcționalitatea și flexibilitatea foilor de lucru. Indiferent dacă vă simțiți confortabil cu codificarea VBA sau preferați o soluție mai simplă, cum ar fi Kutools, acum aveți capacitatea de a vă transforma listele derulante standard în instrumente dinamice, cu selecție multiplă. Cu aceste abilități, sunteți acum echipat pentru a crea documente Excel mai dinamice și mai ușor de utilizat. Pentru cei dornici să aprofundeze capacitățile Excel, site-ul nostru web se mândrește cu o mulțime de tutoriale. Descoperiți mai multe sfaturi și trucuri Excel aici.

Articole pe aceeaşi temă