Yksi kollegoistani kysyi minulta, onko mahdollista tehdä useita valintoja Excelin avattavasta luettelosta.
Kun luot avattavan luettelon, voit tehdä vain yhden valinnan. Jos valitset toisen kohteen, ensimmäinen korvataan uudella valinnalla.
Hän halusi tehdä useita valintoja samasta avattavasta valikosta siten, että valinnat lisätään solun jo nykyiseen arvoon.
Jotain alla olevan kuvan osoittamaa:
Et voi mitenkään tehdä tätä Excelin sisäänrakennettujen ominaisuuksien avulla.
Ainoa tapa on käyttää VBA -koodia, joka suoritetaan aina, kun teet valinnan, ja lisää valitun arvon olemassa olevaan arvoon.
Katso video - Kuinka valita useita kohteita Excelin pudotusvalikosta
Kuinka tehdä useita valintoja avattavasta luettelosta
Tässä opetusohjelmassa näytän sinulle, kuinka voit tehdä useita valintoja avattavasta Excel-luettelosta (toiston kanssa ja ilman toistoa).
Tämä on ollut yksi tämän sivuston suosituimmista Excel -opetusohjelmista. Koska saan paljon vastaavia kysymyksiä, olen päättänyt luoda UKK -osion tämän opetusohjelman lopussa. Joten jos sinulla on kysymyksiä tämän lukemisen jälkeen, tutustu ensin usein kysyttyihin kysymyksiin.Avattavan luettelon luomiseen, joka mahdollistaa useita valintoja, on kaksi osaa:
- Avattavan luettelon luominen.
- VBA-koodin lisääminen taustaosaan.
Avattavan luettelon luominen Excelissä
Voit luoda avattavan luettelon Excelissä seuraavasti:
- Valitse solu tai solualue, johon haluat pudotusvalikon näkyvän (tässä esimerkissä C2).
- Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
- Valitse Tietojen validointi -valintaikkunan asetusvälilehden "Lista" vahvistuskriteereiksi.
- Valitse Lähde -kentässä solut, joissa on haluamasi kohteet avattavasta valikosta.
- Napsauta OK.
Nyt solussa C2 on avattava luettelo, joka näyttää kohteiden nimet kohdassa A2: A6.
Tällä hetkellä meillä on avattava luettelo, jossa voit valita yhden kohteen kerrallaan (kuten alla).
Jos haluat ottaa tämän avattavan valikon käyttöön, jotta voimme tehdä useita valintoja, meidän on lisättävä VBA-koodi takaosaan.
Tämän opetusohjelman seuraavat kaksi osaa antavat sinulle VBA-koodin, jonka avulla voit valita useita valintoja avattavasta luettelosta (toiston kanssa ja ilman toistoa).
VBA-koodi sallii useita valintoja avattavassa luettelossa (toisto)
Alla on Excel VBA -koodi, jonka avulla voimme valita useamman kuin yhden kohteen avattavasta luettelosta (sallien toistojen valinnan):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Voit tehdä mutliple -valintoja Excelin pudotusvalikosta Dim Oldvalue merkkijonona Dim Newvalue merkkijonona virheessä GoTo Exitsub If Target.Address = "$ C $ 2" Sitten Jos Target.SpecialCells (xlCellTypeAllValidation) ei ole mitään sitten GoTo Exitsub Else: If Target.Value = "" Sitten 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 End Jos End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Nyt sinun on sijoitettava tämä koodi VB Editorin moduuliin (kuten alla on kohdassa ”Minne VBA -koodi laitetaan”).
Kun olet asettanut tämän koodin taustaohjelmaan (käsitelty myöhemmin tässä opetusohjelmassa), sen avulla voit tehdä useita valintoja avattavasta valikosta (kuten alla).
Huomaa, että jos valitset kohteen useammin kuin kerran, se syötetään uudelleen (toisto on sallittu).
Kokeile itse… Lataa esimerkkitiedosto
VBA-koodi sallii useita valintoja avattavassa luettelossa (ilman toistoa)
Monet ihmiset ovat kysyneet koodista valitakseen useita kohteita avattavasta luettelosta ilman toistoa.
Tässä on koodi, joka varmistaa, että kohde voidaan valita vain kerran, jotta toistoja ei tapahdu:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Useiden valintojen salliminen Excelin avattavassa luettelossa (ilman toistoa) Dim Oldvalue merkkijonona Dim Newvalue merkkijononsovelluksena.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. Arvo 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 = Vanha arvo Loppu Jos loppu Jos loppu Jos päättyy Jos sovellus. EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Nyt sinun on sijoitettava tämä koodi VB Editorin moduuliin (kuten tämän opetusohjelman seuraavassa osassa on esitetty).
Tämän koodin avulla voit valita useita kohteita avattavasta luettelosta. Voit kuitenkin valita kohteen vain kerran. Jos yrität valita sen uudelleen, mitään ei tapahdu (kuten alla).
Kokeile itse… Lataa esimerkkitiedosto
Minne laittaa VBA -koodi
Ennen kuin alat käyttää tätä koodia Excelissä, sinun on asetettava se taustaosaan, jotta se käynnistyy aina, kun avattavassa valikossa tapahtuu muutoksia.
Laita VBA -koodi Excelin taustaohjelmaan seuraavasti:
- Siirry Kehittäjä -välilehteen ja napsauta Visual Basic (voit käyttää myös pikanäppäintä - Alt + F11). Tämä avaa Visual Basic Editorin.
- Project Explorer -ruudun pitäisi olla vasemmalla (jos se ei ole näkyvissä, käytä Control + R -näppäintä sen näyttämiseksi).
- Kaksoisnapsauta laskentataulukon nimeä (vasemmassa ruudussa), jossa avattava luettelo sijaitsee. Tämä avaa kyseisen laskentataulukon koodi -ikkunan.
- Kopioi ja liitä yllä oleva koodi koodi -ikkunassa.
- Sulje VB Editor.
Nyt kun palaat alasvetovalikkoon ja teet valintoja, voit tehdä useita valintoja (kuten alla):
Kokeile itse… Lataa esimerkkitiedosto
merkintä: Koska käytämme VBA -koodia tämän tekemiseen, sinun on tallennettava työkirja .xls- tai .xlsm -laajennuksella.
Usein kysytyt kysymykset (FAQ)
Olen luonut tämän osion vastatakseni joihinkin usein kysyttyihin kysymyksiin tästä opetusohjelmasta ja VBA -koodista. Jos sinulla on kysyttävää, pyydän sinua käymään läpi tämän kyselyluettelon ensin.
K: VBA -koodissa toiminnot ovat vain solulle C2. Miten saan sen muille soluille? V: Saadaksesi tämän usean valinnan avattavan valikon muissa soluissa, sinun on muokattava taustaohjelman VBA-koodia. Oletetaan, että haluat saada tämän kohteille C2, C3 ja C4, sinun on korvattava seuraava rivi koodissa: If Target.Address = "$ C $ 2" Sitten tällä rivillä: If Target.Address = "$ C $ 2" Tai Target.Address = "$ C $ 3" Tai Target.Address = "$ C $ 4" Sitten
K: Minun on luotava useita pudotusvalikkoja koko sarakkeeseen "C". Miten saan tämän kaikille sarakkeiden soluille, joissa on monivalintatoiminto? V: Jos haluat ottaa useita valintoja käyttöön koko sarakkeen pudotusvalikoista, korvaa seuraava rivi koodissa: If Target.Address = "$ C $ 2" Sitten tällä rivillä: If Target.Column = 3 Sitten Samankaltaisilla riveillä, jos haluat tämän toiminnon sarakkeisiin C ja D, käytä alla olevaa riviä: Jos Target.Column = 3 tai Target.Column = 4 Sitten
K: Minun on luotava useita pudotusvalikkoja peräkkäin. Miten voin tehdä tämän? V: Jos haluat luoda avattavia luetteloita, joissa on useita valintoja peräkkäin (sanotaan toinen rivi), sinun on korvattava alla oleva koodirivi: If Target.Address = "$ C $ 2" Sitten tällä rivillä: Jos Target.Row = 2 Sitten Samoin, jos haluat tämän toimivan useilla riveillä (sanotaan toinen ja kolmas rivi), käytä sen sijaan alla olevaa koodiriviä: If Target.Row = 2 tai Target.Row = 3 Sitten
K: Tällä hetkellä useat valinnat on erotettu toisistaan pilkulla. Kuinka voin muuttaa tämän erottaaksesi ne välilyönnillä (tai millä tahansa muulla erottimella). V: Jos haluat erottaa ne muulla erotuksella kuin pilkulla, sinun on korvattava seuraava VBA -koodirivi: Target.Value = Oldvalue & "," & Newvalue tällä VBA -koodirivillä: Target.Value = Oldvalue & "" & Uusi arvo Samoin, jos haluat vaihtaa pilkun toisella merkillä, kuten |, voit käyttää seuraavaa koodiriviä: Target.Value = Oldvalue & "|" & Newvalue
K: Voinko saada jokaisen valinnan omalle rivilleen samassa solussa? Vastaus: Kyllä voit. Saadaksesi tämän sinun on korvattava alla oleva VBA -koodirivi: Target.Value = Oldvalue & "," & Newvalue tällä koodirivillä: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine lisää uuden rivin samaan soluun . Joten kun teet valinnan avattavasta valikosta, se lisätään uudelle riville.
K: Voinko saada monivalintatoiminnon toimimaan suojatussa arkissa? Vastaus: Kyllä voit. Jotta voit tehdä tämän, sinun on tehtävä kaksi asiaa: Lisää seuraava rivi koodiin (heti DIM -lausekkeen jälkeen): Me.Protect UserInterfaceOnly: = Totta Toiseksi sinun on varmistettava, että solut - joissa on avattava valikko, jossa on useita valintoja - eivät ole lukittuja, kun suojaat koko arkkia. Tässä on opetusohjelma tämän tekemiseen: Lukitse solut Excelissä
