Uno dei miei colleghi mi ha chiesto se è possibile effettuare selezioni multiple in un elenco a discesa in Excel.
Quando crei un elenco a discesa, puoi effettuare solo una selezione. Se si seleziona un altro elemento, il primo viene sostituito con la nuova selezione.
Voleva effettuare più selezioni dallo stesso menu a discesa in modo tale che le selezioni vengano aggiunte al valore già presente nella cella.
Qualcosa come mostrato di seguito nella foto:
Non è possibile farlo con le funzionalità integrate di Excel.
L'unico modo è utilizzare un codice VBA, che viene eseguito ogni volta che si effettua una selezione e aggiunge il valore selezionato al valore esistente.
Guarda il video - Come selezionare più elementi da un elenco a discesa di Excel
Come effettuare selezioni multiple in un elenco a discesa
In questo tutorial, ti mostrerò come effettuare selezioni multiple in un elenco a discesa di Excel (con ripetizione e senza ripetizione).
Questo è stato uno dei tutorial di Excel più popolari su questo sito. Poiché ricevo molte domande simili, ho deciso di creare una sezione FAQ alla fine di questo tutorial. Quindi, se hai domande dopo aver letto questo, controlla prima la sezione FAQ.Ci sono due parti per creare un elenco a discesa che consente selezioni multiple:
- Creazione dell'elenco a discesa.
- Aggiunta del codice VBA al back-end.
Creazione dell'elenco a discesa in Excel
Ecco i passaggi per creare un elenco a discesa in Excel:
- Seleziona la cella o l'intervallo di celle in cui desideri visualizzare l'elenco a discesa (C2 in questo esempio).
- Vai su Dati -> Strumenti dati -> Convalida dati.
- Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, seleziona "Elenco" come Criteri di convalida.
- Nel campo Origine, seleziona le celle che contengono gli elementi desiderati nel menu a discesa.
- Fare clic su OK.
Ora, la cella C2 ha un elenco a discesa che mostra i nomi degli elementi in A2: A6.
A partire da ora, abbiamo un elenco a discesa in cui è possibile selezionare un elemento alla volta (come mostrato di seguito).
Per abilitare questo menu a discesa per consentirci di effettuare selezioni multiple, dobbiamo aggiungere il codice VBA nel back-end.
Le prossime due sezioni di questo tutorial ti forniranno il codice VBA per consentire selezioni multiple nell'elenco a discesa (con e senza ripetizione).
Codice VBA per consentire selezioni multiple in un elenco a discesa (con ripetizione)
Di seguito è riportato il codice VBA di Excel che ci consentirà di selezionare più di un elemento dall'elenco a discesa (consentendo ripetizioni nella selezione):
Private Sub Worksheet_Change (ByVal Target As Range) 'Codice per Sumit Bansal da https://trumpexcel.com ' Per effettuare selezioni multiple in un elenco a discesa in Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) non è niente Allora GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Ora devi inserire questo codice in un modulo in VB Editor (come mostrato di seguito nella sezione "Dove inserire il codice VBA").
Dopo aver inserito questo codice nel backend (trattato più avanti in questo tutorial), ti consentirà di effettuare selezioni multiple nel menu a discesa (come mostrato di seguito).
Nota che se selezioni un elemento più di una volta, verrà inserito di nuovo (è consentita la ripetizione).
Provalo tu stesso… Scarica il file di esempio
Codice VBA per consentire selezioni multiple in un elenco a discesa (senza ripetizione)
Molte persone hanno chiesto informazioni sul codice per selezionare più elementi da un elenco a discesa senza ripetizioni.
Ecco il codice che farà in modo che un articolo possa essere selezionato solo una volta in modo che non ci siano ripetizioni:
Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com ' Per consentire selezioni multiple in un elenco a discesa in Excel (senza ripetizione) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) non è niente Allora GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Ora devi inserire questo codice in un modulo in VB Editor (come mostrato nella prossima sezione di questo tutorial).
Questo codice ti consentirà di selezionare più elementi dall'elenco a discesa. Tuttavia, potrai selezionare un elemento solo una volta. Se provi a selezionarlo di nuovo, non accadrebbe nulla (come mostrato di seguito).
Provalo tu stesso… Scarica il file di esempio
Dove inserire il codice VBA
Prima di iniziare a utilizzare questo codice in Excel, è necessario inserirlo nel back-end, in modo che venga attivato ogni volta che si verifica una modifica nella selezione a discesa.
Segui i passaggi seguenti per inserire il codice VBA nel backend di Excel:
- Vai alla scheda Sviluppatore e fai clic su Visual Basic (puoi anche utilizzare la scorciatoia da tastiera - Alt + F11). Verrà aperto l'editor di Visual Basic.
- Dovrebbe esserci un riquadro Esplora progetti a sinistra (se non è lì, usa Control + R per renderlo visibile).
- Fare doppio clic su Nome foglio di lavoro (nel riquadro di sinistra) in cui si trova l'elenco a discesa. Questo apre la finestra del codice per quel foglio di lavoro.
- Nella finestra del codice, copia e incolla il codice sopra.
- Chiudi l'editor VB.
Ora quando torni al menu a discesa ed effettui le selezioni, ti consentirà di effettuare selezioni multiple (come mostrato di seguito):
Provalo tu stesso… Scarica il file di esempio
Nota: Poiché stiamo utilizzando un codice VBA per eseguire questa operazione, è necessario salvare la cartella di lavoro con un'estensione .xls o .xlsm.
Domande frequenti (FAQ)
Ho creato questa sezione per rispondere ad alcune delle domande più frequenti su questo tutorial e sul codice VBA. In caso di domande, ti chiedo di esaminare prima questo elenco di domande.
D: Nel codice VBA, la funzionalità è solo per la cella C2. Come posso ottenerlo per altre celle? Risposta: per ottenere questo menu a discesa di selezione multipla in altre celle, è necessario modificare il codice VBA nel back-end. Supponiamo che tu voglia ottenere questo per C2, C3 e C4, devi sostituire la seguente riga nel codice: If Target.Address = "$C$2" Quindi con questa riga: If Target.Address = "$C$2" Oppure Target.Address = "$C$3" Oppure Target.Address = "$C$4" Allora
D: Devo creare più menu a discesa nell'intera colonna "C". Come ottengo questo per tutte le celle nelle colonne con funzionalità di selezione multipla? Risposta: Per abilitare più selezioni nei menu a discesa in un'intera colonna, sostituire la seguente riga nel codice: If Target.Address = "$C$2" Then con questa riga: If Target.Column = 3 Then Su righe simili, if si desidera questa funzionalità nelle colonne C e D, utilizzare la riga seguente: If Target.Column = 3 o Target.Column = 4 Then
D: Devo creare più menu a discesa di fila. Come posso fare questo? Risposta: Se è necessario creare elenchi a discesa con selezioni multiple in una riga (diciamo la seconda riga), è necessario sostituire la riga di codice sottostante: If Target.Address = "$C$2" Quindi con questa riga: Se Target.Row = 2 Quindi allo stesso modo, se vuoi che funzioni per più righe (diciamo la seconda e la terza riga), usa invece la riga di codice sottostante: If Target.Row = 2 o Target.Row = 3 Quindi
D: A partire da ora, le selezioni multiple sono separate da una virgola. Come posso cambiarlo per separarli con lo spazio (o qualsiasi altro separatore). Risposta: per separarli con un separatore diverso da una virgola, è necessario sostituire la seguente riga di codice VBA: Target.Value = Oldvalue & ", " & Newvalue con questa riga di codice VBA: Target.Value = Oldvalue & " " & Nuovovalore Allo stesso modo, se si desidera modificare la virgola con un altro carattere, ad esempio |, è possibile utilizzare la seguente riga di codice: Target.Value = Oldvalue & "| " & Newvalue
D: Posso ottenere ogni selezione in una riga separata nella stessa cella? Risposta: Sì, puoi. Per ottenere ciò, è necessario sostituire la riga seguente del codice VBA: Target.Value = Oldvalue & ", " & Newvalue con questa riga di codice: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine inserisce una nuova riga nella stessa cella . Quindi, ogni volta che effettui una selezione dal menu a discesa, verrà inserita in una nuova riga.
D: Posso far funzionare la funzionalità di selezione multipla in un foglio protetto? Risposta: Sì, puoi. Per fare ciò, devi fare due cose: Aggiungi la seguente riga nel codice (subito dopo l'istruzione DIM): Me.Protect UserInterfaceOnly:=Vero In secondo luogo, è necessario assicurarsi che le celle, che hanno il menu a discesa con funzionalità di selezione multipla, non siano bloccate quando si protegge l'intero foglio. Ecco un tutorial su come farlo: Blocca celle in Excel
