Seleziona più elementi nella lista a discesa di Excel – guida completa

Le liste a discesa di Excel sono uno strumento fantastico per garantire la coerenza dei dati e facilitare l'inserimento. Tuttavia, di default, ti limitano alla selezione di un solo elemento. Ma cosa succede se hai bisogno di selezionare più elementi dalla stessa lista a discesa? Questa guida completa esplorerà metodi per abilitare selezioni multiple nelle liste a discesa di Excel, gestire i duplicati, impostare separatori personalizzati e definire l'ambito di queste liste.

• Utilizzo del codice VBA
• Utilizzo di Kutools per Excel in pochi clic

• Consentire elementi duplicati
• Rimuovere eventuali elementi esistenti
• Impostare un separatore personalizzato
• Impostare un intervallo specifico
• Esecuzione in un foglio di lavoro protetto

Abilitazione di selezioni multiple nella lista a discesa

Questa sezione fornisce due metodi per aiutarti ad abilitare selezioni multiple nella lista a discesa in Excel.

Utilizzo del codice VBA

Per consentire selezioni multiple in una lista a discesa, puoi utilizzare "Visual Basic for Applications" (VBA) in Excel. Lo script può modificare il comportamento di una lista a discesa per renderla una lista a scelta multipla. Procedi come segue.

Passo 1: Apri l'editor del Foglio (Codice)

1. Apri il foglio di lavoro che contiene la lista a discesa per la quale desideri abilitare la selezione multipla.
2. Fai clic destro sulla scheda del foglio e seleziona "Visualizza Codice" dal menu contestuale.
   

Passo 2: Usa il codice VBA

Ora copia il seguente codice VBA e incollalo nella finestra del Foglio (Codice) aperta.

Codice VBA: Abilita selezioni multiple nella lista a discesa di 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

Risultato

Quando torni al foglio di lavoro, la lista a discesa ti permetterà di scegliere più opzioni, guarda la demo qui sotto:

Utilizzo di Kutools per Excel in pochi clic

Se non sei a tuo agio con VBA, un'alternativa più semplice è la funzione "Lista a discesa multi-selezione" di "Kutools per Excel". Questo strumento facile da usare semplifica l'abilitazione di selezioni multiple nelle liste a discesa, consentendoti di personalizzare il separatore e gestire i duplicati senza sforzo per soddisfare le tue diverse esigenze.

Dopo aver installato Kutools per Excel, vai alla scheda "Kutools", seleziona "Lista a discesa" > "Lista a discesa multi-selezione". Quindi devi configurare come segue.

1. Specifica l'intervallo contenente la lista a discesa da cui devi selezionare più elementi.
2. Specifica il separatore per gli elementi selezionati nella cella della lista a discesa.
3. Clicca su "OK" per completare le impostazioni.
   

Risultato

Ora, quando fai clic su una cella con una lista a discesa nell'intervallo specificato, apparirà una casella di riepilogo accanto ad essa. Basta fare clic sul pulsante "+" accanto agli elementi per aggiungerli alla cella della lista a discesa e fare clic sul pulsante "-" per rimuovere eventuali elementi che non desideri più. Guarda la demo qui sotto:

Ulteriori operazioni per la lista a discesa multi-selezione

Questa sezione raccoglie i diversi scenari che potrebbero essere richiesti quando si abilitano selezioni multiple nella lista a discesa di Convalida dei Dati.

Consentire elementi duplicati nella lista a discesa

I duplicati possono essere un problema quando vengono consentite selezioni multiple in una lista a discesa. Il codice VBA sopra non consente elementi duplicati nella lista a discesa. Se hai bisogno di mantenere elementi duplicati, prova il codice VBA in questa sezione.

Codice VBA: Consenti duplicati nella lista a discesa di convalida dei dati

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

Risultato

Ora puoi selezionare più elementi dalle liste a discesa nel foglio di lavoro corrente. Per ripetere un elemento in una cella della lista a discesa, continua a selezionare quell'elemento dall'elenco. Guarda la schermata:

Rimuovere eventuali elementi esistenti dalla lista a discesa

Dopo aver selezionato più elementi da una lista a discesa, a volte potresti aver bisogno di rimuovere un elemento esistente dalla cella della lista a discesa. Questa sezione fornisce un altro pezzo di codice VBA per aiutarti a completare questa attività.

Codice VBA: Rimuovi eventuali elementi esistenti dalla cella della lista a discesa

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

Risultato

Questo codice VBA ti consente di selezionare più elementi da una lista a discesa e rimuovere facilmente qualsiasi elemento che hai già scelto. Dopo aver selezionato più elementi, se vuoi rimuoverne uno specifico, basta selezionarlo nuovamente dall'elenco.

Impostare un separatore personalizzato

Il delimitatore è impostato come virgola nei codici VBA sopra. Puoi modificare questa variabile con qualsiasi carattere preferito da utilizzare come separatore per le selezioni della lista a discesa. Ecco come puoi farlo:

Come puoi vedere, i codici VBA sopra hanno tutti la seguente riga:

delimiter = ", "

Devi solo cambiare la virgola con qualsiasi separatore di cui hai bisogno. Ad esempio, se vuoi separare gli elementi con un punto e virgola, cambia la riga in:

delimiter = "; "

delimiter = vbNewLine

Impostare un intervallo specifico

I codici VBA sopra si applicano a tutte le liste a discesa nel foglio di lavoro corrente. Se vuoi che i codici VBA si applichino solo a un certo intervallo di liste a discesa, puoi specificare l'intervallo nel codice VBA sopra come segue.

Come puoi vedere, i codici VBA sopra hanno tutti la seguente riga:

Set TargetRange = Me.UsedRange

Devi solo cambiare la riga in:

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

Esecuzione in un foglio di lavoro protetto

Immagina di aver protetto un foglio di lavoro con la password "123" e impostato le celle della lista a discesa su "Sbloccate" prima di attivare la protezione, garantendo così che la funzione di selezione multipla rimanga attiva dopo la protezione. Tuttavia, i codici VBA menzionati sopra non possono funzionare in questo caso, e questa sezione descrive un altro script VBA appositamente progettato per gestire la funzionalità di selezione multipla in un foglio di lavoro protetto.

Codice VBA: Abilita selezione multipla nella lista a discesa senza duplicati

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

Abilitando selezioni multiple nelle liste a discesa di Excel, puoi migliorare notevolmente la funzionalità e la flessibilità dei tuoi fogli di lavoro. Che tu sia a tuo agio con la codifica VBA o preferisca una soluzione più semplice come Kutools, ora hai la capacità di trasformare le tue liste a discesa standard in strumenti dinamici e multi-selezione. Con queste competenze, sei ora pronto a creare documenti Excel più dinamici e user-friendly. Per coloro che sono desiderosi di approfondire ulteriormente le funzionalità di Excel, il nostro sito web offre una ricchezza di tutorial. Scopri altri suggerimenti e trucchi per Excel qui.

Articoli correlati