Unul dintre colegii mei m-a întrebat dacă este posibil să fac selecții multiple într-o listă derulantă în Excel.
Când creați o listă derulantă, nu puteți face decât o singură selecție. Dacă selectați un alt element, primul este înlocuit cu noua selecție.
El a dorit să facă mai multe selecții din aceeași listă verticală, astfel încât selecțiile să fie adăugate la valoarea deja prezentă în celulă.
Ceva așa cum se arată mai jos în imagine:
Nu puteți face acest lucru cu funcțiile încorporate Excel.
Singura modalitate este de a utiliza un cod VBA, care rulează ori de câte ori faceți o selecție și adăugați valoarea selectată la valoarea existentă.
Vizionați videoclipuri - Cum să selectați mai multe articole dintr-o listă derulantă Excel
Cum se fac selecții multiple într-o listă derulantă
În acest tutorial, vă voi arăta cum să faceți mai multe selecții într-o listă drop-down Excel (cu repetare și fără repetare).
Acesta a fost unul dintre cele mai populare tutoriale Excel de pe acest site. Deoarece primesc o mulțime de întrebări similare, am decis să creez o secțiune FAQ la sfârșitul acestui tutorial. Deci, dacă aveți întrebări după ce ați citit acest lucru, vă rugăm să consultați mai întâi secțiunea FAQ.Există două părți în crearea unei liste drop-down care permite selecții multiple:
- Crearea listei derulante.
- Adăugarea codului VBA la back-end.
Crearea listei derulante în Excel
Iată pașii pentru a crea o listă derulantă în Excel:
- Selectați celula sau gama de celule în care doriți să apară lista derulantă (C2 în acest exemplu).
- Accesați Date -> Instrumente de date -> Validare date.
- În caseta de dialog Validare date, din fila Setări, selectați „Listă” ca criterii de validare.
- În câmpul Sursă, selectați celulele care au elementele dorite în meniul derulant.
- Faceți clic pe OK.
Acum, celula C2 are o listă derulantă care afișează numele articolelor din A2: A6.
De acum, avem o listă derulantă în care puteți selecta câte un articol la un moment dat (așa cum se arată mai jos).
Pentru a activa această listă derulantă pentru a ne permite să facem mai multe selecții, trebuie să adăugăm codul VBA în partea din spate.
Următoarele două secțiuni ale acestui tutorial vă vor oferi codul VBA pentru a permite selecții multiple în lista derulantă (cu și fără repetare).
Cod VBA pentru a permite selecții multiple într-o listă derulantă (cu repetare)
Mai jos este codul VBA Excel care ne va permite să selectăm mai multe elemente din lista derulantă (permițând repetări în selecție):
Private Sub Worksheet_Change (ByVal Target As Range) „Cod de Sumit Bansal de la https://trumpexcel.com” Pentru a face selecții mutliple într-o listă drop-down în Excel Dim Oldvalue Ca șir Dim Newvalue Ca șir la eroare GoTo Exitsub If Target.Address = "$ C $ 2" Atunci, dacă Target.SpecialCells (xlCellTypeAllValidation) nu este nimic, atunci GoTo Exitsub Else: If Target.Value = "" Atunci 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
Acum trebuie să plasați acest cod într-un modul în Editorul VB (așa cum se arată mai jos în secțiunea „Unde se pune codul VBA”).
După ce ați plasat acest cod în backend (acoperit mai târziu în acest tutorial), acesta vă va permite să faceți mai multe selecții în meniul derulant (așa cum se arată mai jos).
Rețineți că, dacă selectați un articol de mai multe ori, acesta va fi introdus din nou (repetarea este permisă).
Încercați-l singur … Descărcați fișierul de exemplu
Cod VBA pentru a permite selecții multiple într-o listă derulantă (fără repetare)
O mulțime de oameni au întrebat despre cod pentru a selecta mai multe articole dintr-o listă derulantă fără repetare.
Iată codul care va asigura că un articol poate fi selectat o singură dată, astfel încât să nu existe repetări:
Private Sub Worksheet_Change (ByVal Target As Range) „Cod de Sumit Bansal de la https://trumpexcel.com” Pentru a permite selecții multiple într-o listă derulantă în Excel (fără repetare) 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) is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Valoare Application.Undo Oldvalue = Target.Value If Oldvalue = "" Atunci 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 End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Acum trebuie să plasați acest cod într-un modul în VB Editor (așa cum se arată în secțiunea următoare a acestui tutorial).
Acest cod vă va permite să selectați mai multe articole din lista derulantă. Cu toate acestea, veți putea selecta un articol doar o singură dată. Dacă încercați și îl selectați din nou, nu s-ar întâmpla nimic (așa cum se arată mai jos).
Încercați-l singur … Descărcați fișierul de exemplu
Unde să puneți codul VBA
Înainte de a începe să utilizați acest cod în Excel, trebuie să îl puneți în back-end, astfel încât să fie declanșat ori de câte ori există vreo modificare în selecția drop-down.
Urmați pașii de mai jos pentru a pune codul VBA în backend-ul Excel:
- Accesați fila Dezvoltator și faceți clic pe Visual Basic (puteți utiliza și comanda rapidă de la tastatură - Alt + F11). Aceasta va deschide Editorul Visual Basic.
- Ar trebui să existe un panou Project Explorer în stânga (dacă nu este acolo, utilizați Control + R pentru a-l face vizibil).
- Faceți dublu clic pe Nume foaie de lucru (în panoul din stânga) în care se află lista derulantă. Aceasta deschide fereastra de cod pentru foaia de lucru respectivă.
- În fereastra codului, copiați și lipiți codul de mai sus.
- Închideți Editorul VB.
Acum, când reveniți la meniul derulant și faceți selecții, acesta vă va permite să faceți mai multe selecții (așa cum se arată mai jos):
Încercați-l singur … Descărcați fișierul de exemplu
Notă: Deoarece folosim un cod VBA pentru a face acest lucru, trebuie să salvați registrul de lucru cu o extensie .xls sau .xlsm.
Întrebări frecvente (FAQ)
Am creat această secțiune pentru a răspunde la unele dintre cele mai frecvente întrebări despre acest tutorial și codul VBA. Dacă aveți întrebări, vă rog să parcurgeți mai întâi această listă de interogări.
Î: În codul VBA, funcționalitatea este doar pentru celula C2. Cum îl obțin pentru alte celule? Răspuns: Pentru a obține această listă de selecție multiplă în alte celule, trebuie să modificați codul VBA din backend. Să presupunem că doriți să obțineți acest lucru pentru C2, C3 și C4, trebuie să înlocuiți următoarea linie din cod: If Target.Address = "$ C $ 2" Apoi cu această linie: If Target.Address = "$ C $ 2" Sau Target.Address = "$ C $ 3" Sau Target.Address = "$ C $ 4" Apoi
Î: Trebuie să creez mai multe drop-down-uri în întreaga coloană „C”. Cum obțin acest lucru pentru toate celulele din coloane cu funcționalitate de selectare multiplă? Răspuns: Pentru a activa mai multe selecții în meniurile derulante dintr-o coloană întreagă, înlocuiți următoarea linie din cod: If Target.Address = "$ C $ 2" Apoi cu această linie: If Target.Column = 3 Apoi pe linii similare, dacă doriți această funcționalitate în coloanele C și D, utilizați linia de mai jos: Dacă Target.Column = 3 sau Target.Column = 4 Apoi
Î: Trebuie să creez mai multe drop-down-uri la rând. Cum pot face acest lucru? Răspuns: Dacă trebuie să creați liste drop-down cu mai multe selecții la rând (să presupunem al doilea rând), trebuie să înlocuiți linia de cod de mai jos: If Target.Address = "$ C $ 2" Apoi cu această linie: Dacă Target.Row = 2 Then În mod similar, dacă doriți ca acest lucru să funcționeze pentru mai multe rânduri (să presupunem al doilea și al treilea rând), utilizați în schimb linia de cod de mai jos: If Target.Row = 2 or Target.Row = 3 Then
Î: De acum, selecțiile multiple sunt separate printr-o virgulă. Cum pot schimba acest lucru pentru a le separa de spațiu (sau orice alt separator). Răspuns: Pentru a le separa cu un alt separator decât o virgulă, trebuie să înlocuiți următoarea linie de cod VBA: Target.Value = Oldvalue & "," & Newvalue cu această linie de cod VBA: Target.Value = Oldvalue & "" & Newvalue În mod similar, dacă doriți să schimbați virgula cu alte caractere, cum ar fi |, puteți utiliza următoarea linie de cod: Target.Value = Oldvalue & "|" & Newvalue
Î: Pot obține fiecare selecție într-o linie separată în aceeași celulă? Răspuns: Da poți. Pentru a obține acest lucru, trebuie să înlocuiți linia de mai jos a codului VBA: Target.Value = Oldvalue & "," & Newvalue cu această linie de cod: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine introduce o nouă linie în aceeași celulă . Deci, ori de câte ori faceți o selecție din meniul derulant, aceasta va fi inserată într-o nouă linie.
Î: Pot face ca funcționalitatea de selecție multiplă să funcționeze într-o foaie protejată? Răspuns: Da poți. Pentru a face acest lucru, trebuie să faceți două lucruri: Adăugați următoarea linie în cod (imediat după declarația DIM): Me.Protect UserInterfaceOnly: = Adevărat În al doilea rând, trebuie să vă asigurați că celulele - care au meniul derulant cu funcționalitate de selecție multiplă - nu sunt blocate atunci când protejați întreaga foaie. Iată un tutorial despre cum să faceți acest lucru: Blocați celulele în Excel
