Eden od mojih kolegov me je vprašal, ali je mogoče na spustnem seznamu v Excelu narediti več izbir.
Ko ustvarite spustni seznam, lahko izberete samo eno izbiro. Če izberete drug element, se prvi zamenja z novim.
Želel je narediti več izbir iz istega spustnega menija tako, da se izbori dodajo že sedanji vrednosti v celici.
Nekaj, kot je prikazano spodaj na sliki:
Z vgrajenimi funkcijami programa Excel to nikakor ne morete storiti.
Edini način je, da uporabite kodo VBA, ki se zažene vsakič, ko izberete, in dodano vrednost doda obstoječi vrednosti.
Oglejte si video - Kako izbrati več elementov s spustnega seznama v Excelu
Kako narediti več izbir na spustnem seznamu
V tej vadnici vam bom pokazal, kako na spustnem seznamu Excel izbrati več možnosti (s ponavljanjem in brez ponavljanja).
To je bila ena izmed najbolj priljubljenih vadnic o Excelu na tem spletnem mestu. Ker dobivam veliko podobnih vprašanj, sem se odločil, da na koncu te vadnice ustvarim razdelek s pogostimi vprašanji. Če imate po branju tega vprašanja kakršna koli vprašanja, najprej preglejte razdelek Pogosta vprašanja.Spustni seznam, ki omogoča več izbir, je sestavljen iz dveh delov:
- Ustvarjanje spustnega seznama.
- Dodajanje kode VBA v ozadje.
Ustvarjanje spustnega seznama v Excelu
Tu so koraki za ustvarjanje spustnega seznama v Excelu:
- Izberite celico ali obseg celic, kjer želite, da se prikaže spustni seznam (C2 v tem primeru).
- Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
- V pogovornem oknu Validacija podatkov na zavihku z nastavitvami izberite „Seznam“ kot merila za preverjanje.
- V polju Vir izberite celice, ki imajo v spustnem meniju želene postavke.
- Kliknite V redu.
Zdaj ima celica C2 spustni seznam, ki prikazuje imena elementov v A2: A6.
Zaenkrat imamo spustni seznam, na katerem lahko izberete eno za drugo (kot je prikazano spodaj).
Če želite omogočiti ta spustni meni, da lahko izbiramo več, moramo na zadnji strani dodati kodo VBA.
Naslednja dva razdelka te vadnice vam bosta podali kodo VBA, ki dovoljuje več izbir na spustnem seznamu (s ponavljanjem in brez njega).
Koda VBA, ki dovoljuje več izbir na spustnem seznamu (s ponavljanjem)
Spodaj je koda Excel VBA, ki nam bo omogočila, da na spustnem seznamu izberemo več elementov (pri izbiri omogočajo ponovitve):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Za večkratne izbire na spustnem seznamu v Excelu Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Potem, če Target.SpecialCells (xlCellTypeAllValidation) ni nič, potem pojdite na Exitub Drugače: Če je Target.Value = "" Potem GoTo Exitsub Druga aplikacija.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value. If Oldvalue = "" Potem je Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End If End Če se konča, če Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Zdaj morate to kodo postaviti v modul v urejevalniku VB (kot je prikazano spodaj v razdelku »Kam postaviti kodo VBA«).
Ko postavite to kodo v ozadje (obravnavano kasneje v tej vadnici), vam bo omogočilo, da v spustnem meniju izberete več možnosti (kot je prikazano spodaj).
Upoštevajte, da če element izberete več kot enkrat, bo vnesen znova (dovoljeno je ponavljanje).
Poskusite sami … Prenesite datoteko z vzorcem
Koda VBA, ki dovoljuje več izbir na spustnem seznamu (brez ponavljanja)
Mnogi ljudje sprašujejo o kodi, da izberejo več elementov s spustnega seznama brez ponavljanja.
Tukaj je koda, ki bo zagotovila, da je mogoče element izbrati samo enkrat, da se ne ponovijo:
Private Sub Worksheet_Change (ByVal Target As Range) 'Koda Sumita Bansala s https://trumpexcel.com' Dovoljenje več izbir na spustnem seznamu v Excelu (brez ponavljanja) Zatemni staro vrednost kot niz Zatemni novo vrednost kot aplikacijo nizov. EnableEvents = Napaka pri vklopu GoTo Exitsub If Target.Address = "$ C $ 2" Potem če Target.SpecialCells (xlCellTypeAllValidation) ni nič, potem GoTo Exitsub Else: If Target.Value = "" Potem GoTo Exitsub Else Application.EnableEvents = False Newvalue = Targe. Vrednost Application.Undo Oldvalue = Target.Value If Oldvalue = "" Potem Target.Value = Newvalue Drugače Če je InStr (1, Oldvalue, Newvalue) = 0 Potem je Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue Konec End Če Konec Če Konec Če Konec Če Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Zdaj morate to kodo postaviti v modul v urejevalniku VB (kot je prikazano v naslednjem razdelku te vadnice).
Ta koda vam omogoča, da na spustnem seznamu izberete več elementov. Izbrali boste lahko samo enkrat. Če poskusite znova izbrati, se ne bo zgodilo nič (kot je prikazano spodaj).
Poskusite sami … Prenesite datoteko z vzorcem
Kam vstaviti kodo VBA
Preden začnete uporabljati to kodo v Excelu, jo morate postaviti v ozadje, tako da se sproži vsakič, ko pride do spremembe v spustnem meniju.
Če želite kodo VBA postaviti v ozadje Excela, sledite spodnjim korakom:
- Pojdite na zavihek Razvijalec in kliknite Visual Basic (uporabite lahko tudi bližnjico na tipkovnici - Alt + F11). S tem se odpre urejevalnik Visual Basic.
- Na levi strani mora biti podokno Project Explorer (če ga ni, uporabite Control + R, da bo vidno).
- Dvokliknite Ime delovnega lista (v levem podoknu), kjer se nahaja spustni seznam. S tem se odpre okno kode za ta delovni list.
- V oknu za kodo kopirajte in prilepite zgornjo kodo.
- Zaprite urejevalnik VB.
Zdaj, ko se vrnete na spustni meni in izberete, vam bo omogočilo več izbire (kot je prikazano spodaj):
Poskusite sami … Prenesite datoteko z vzorcem
Opomba: Ker za to uporabljamo kodo VBA, morate delovni zvezek shraniti z razširitvijo .xls ali .xlsm.
Pogosto zastavljena vprašanja (FAQ)
Ta razdelek sem ustvaril, da odgovorim na nekatera najpogostejša vprašanja o tej vadnici in kodi VBA. Če imate kakršna koli vprašanja, vas prosim, da najprej preberete ta seznam poizvedb.
V: V kodi VBA je funkcionalnost samo za celico C2. Kako ga dobim za druge celice? Odgovor: Če želite ta spustni meni z več izbirami dobiti v drugih celicah, morate spremeniti kodo VBA v zaledju. Recimo, da želite to dobiti za C2, C3 in C4, morate v kodi zamenjati naslednjo vrstico: Če je Target.Address = "$ C $ 2" Nato s to vrstico: Če je Target.Address = "$ C $ 2" Ali Target.Address = "$ C $ 3" Ali Target.Address = "$ C $ 4" Nato
V: Moram ustvariti več spustnih menijev v celotnem stolpcu 'C'. Kako to dobim za vse celice v stolpcih s funkcijo več izbir? Odgovor: Če želite v spustnem meniju omogočiti več izbir v celotnem stolpcu, zamenjajte naslednjo vrstico v kodi: Če je Target.Address = "$ C $ 2" Nato s to vrstico: Če je Target.Column = 3 Potem V podobnih vrsticah, če če želite to funkcijo v stolpcih C in D, uporabite spodnjo vrstico: Če je Target.Column = 3 ali Target.Column = 4 Potem
V: Moram ustvariti več spustnih menijev zapored. Kako lahko to storim? Odgovor: Če morate ustvariti spustne sezname z več izbirami zaporedoma (recimo drugo vrstico), morate naslednjo vrstico kode zamenjati: Če je Target.Address = "$ C $ 2" Nato s to vrstico: Če je Target.Row = 2 Potem podobno, če želite, da to deluje za več vrstic (recimo drugo in tretjo vrstico), namesto tega uporabite spodnjo vrstico kode: Če je Target.Row = 2 ali Target.Row = 3 Potem
V: Zaenkrat je več izbir ločenih z vejico. Kako lahko to spremenim, da jih ločim s presledkom (ali katerim koli drugim ločevalnikom). Odgovor: Če jih želite ločiti z ločevalnikom, ki ni vejica, morate naslednjo vrstico kode VBA zamenjati: Target.Value = Oldvalue & "," & Newvalue s to vrstico kode VBA: Target.Value = Oldvalue & "" & Newvalue Podobno, če želite spremeniti vejico z drugim znakom, na primer |, lahko uporabite naslednjo vrstico kode: Target.Value = Oldvalue & "|" & Newvalue
V: Ali lahko dobim vsako izbiro v ločeni vrstici v isti celici? Odgovor: Da, lahko. Če želite to narediti, morate naslednjo vrstico kode VBA zamenjati: Target.Value = Oldvalue & "," & Newvalue s to vrstico kode: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine vstavi novo vrstico v isto celico . Torej, kadar koli izberete spustni meni, bo ta vstavljen v novo vrstico.
V: Ali lahko omogočim, da večstranska izbira deluje na zaščitenem listu? Odgovor: Da, lahko. Če želite to narediti, morate narediti dve stvari: Dodajte naslednjo vrstico v kodo (takoj za izjavo DIM): Me.Protect UserInterfaceOnly: = True Drugič, poskrbite, da celice - ki imajo spustni meni z več izbirami - niso zaklenjene, ko zaščitite celoten list. Tukaj je vadnica, kako to storiti: Zaklepanje celic v Excelu
