En av mine kolleger spurte meg om det er mulig å gjøre flere valg i en rullegardinliste i Excel.
Når du oppretter en rullegardinliste, kan du bare gjøre ett valg. Hvis du velger et annet element, erstattes det første med det nye.
Han ønsket å gjøre flere valg fra den samme nedtrekkslisten på en slik måte at valgene blir lagt til den allerede nåværende verdien i cellen.
Noe som vist nedenfor på bildet:
Det er ingen måte du kan gjøre dette med Excel innebygde funksjoner.
Den eneste måten er å bruke en VBA -kode, som kjører når du foretar et valg og legger den valgte verdien til den eksisterende verdien.
Se video - Slik velger du flere elementer fra en Excel -rullegardinliste
Hvordan lage flere valg i en rullegardinliste
I denne opplæringen vil jeg vise deg hvordan du gjør flere valg i en Excel-rullegardinliste (med repetisjon og uten repetisjon).
Dette har vært en av de mest populære Excel -opplæringene på dette nettstedet. Siden jeg får mange lignende spørsmål, har jeg bestemt meg for å lage en FAQ -seksjon på slutten av denne opplæringen. Så hvis du har spørsmål etter å ha lest dette, vennligst sjekk FAQ -delen først.Det er to deler for å lage en rullegardinliste som tillater flere valg:
- Oppretter rullegardinlisten.
- Legger til VBA-koden i back-end.
Opprette rullegardinlisten i Excel
Her er trinnene for å lage en rullegardinliste i Excel:
- Velg cellen eller celleområdet der du vil at rullegardinlisten skal vises (C2 i dette eksemplet).
- Gå til Data -> Dataverktøy -> Datavalidering.
- I dialogboksen Datavalidering, i kategorien Innstillinger, velg 'Liste' som valideringskriterier.
- I feltet Kilde velger du cellene som har elementene du vil ha, i rullegardinmenyen.
- Klikk OK.
Nå har celle C2 en rullegardinliste som viser elementnavnene i A2: A6.
Per nå har vi en rullegardinliste der du kan velge ett element om gangen (som vist nedenfor).
For å aktivere denne rullegardinmenyen slik at vi kan gjøre flere valg, må vi legge til VBA-koden i bakenden.
De to neste delene av denne opplæringen gir deg VBA-koden for å tillate flere valg i rullegardinlisten (med og uten gjentagelse).
VBA-kode for å tillate flere valg i en rullegardinliste (med repetisjon)
Nedenfor er Excel VBA-koden som gjør at vi kan velge mer enn ett element fra rullegardinlisten (tillater gjentakelser i valg):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' For å gjøre flere valg i en rullegardinliste i Excel Dim Oldvalue As String Dim Newvalue As String 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.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
Nå må du plassere denne koden i en modul i VB Editor (som vist nedenfor i delen "Hvor skal du sette VBA -koden").
Når du har plassert denne koden i backend (dekket senere i denne opplæringen), lar den deg gjøre flere valg i rullegardinmenyen (som vist nedenfor).
Vær oppmerksom på at hvis du velger et element mer enn én gang, blir det lagt inn igjen (repetisjon er tillatt).
Prøv det selv … Last ned eksempelfilen
VBA-kode for å tillate flere valg i en rullegardinliste (uten gjentagelse)
Mange har spurt om koden for å velge flere elementer fra en rullegardinliste uten gjentagelse.
Her er koden som vil sikre at et element bare kan velges én gang, slik at det ikke er gjentakelser:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' For å tillate flere valg i en rullegardinliste i Excel (uten repetisjon) 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. 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 End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Nå må du plassere denne koden i en modul i VB Editor (som vist i den neste delen av denne opplæringen).
Denne koden lar deg velge flere elementer fra rullegardinlisten. Du vil imidlertid bare kunne velge et element en gang. Hvis du prøver å velge det igjen, ville ingenting skje (som vist nedenfor).
Prøv det selv … Last ned eksempelfilen
Hvor å plassere VBA -koden
Før du begynner å bruke denne koden i excel, må du sette den i back-end, slik at den blir avfyrt hver gang det er noen endring i rullegardinmenyen.
Følg trinnene nedenfor for å sette VBA -koden i backend i Excel:
- Gå til Utvikler -fanen og klikk på Visual Basic (du kan også bruke hurtigtasten - Alt + F11). Dette åpner Visual Basic Editor.
- Det skal være en Project Explorer -rute til venstre (hvis den ikke er der, bruk Ctrl + R for å gjøre den synlig).
- Dobbeltklikk på regnearknavn (i venstre rute) der rullegardinlisten ligger. Dette åpner kodevinduet for det regnearket.
- Kopier og lim inn koden ovenfor i kodevinduet.
- Lukk VB Editor.
Når du går tilbake til rullegardinmenyen og gjør valg, lar den deg gjøre flere valg (som vist nedenfor):
Prøv det selv … Last ned eksempelfilen
Merk: Siden vi bruker en VBA -kode for å få dette gjort, må du lagre arbeidsboken med en .xls eller .xlsm forlengelse.
Ofte stilte spørsmål
Jeg har laget denne delen for å svare på noen av de mest stilte spørsmålene om denne opplæringen og VBA -koden. Hvis du har spørsmål, ber jeg deg om å gå gjennom denne listen med spørsmål først.
Spørsmål: I VBA -koden er funksjonaliteten bare for celle C2. Hvordan får jeg det til andre celler? Svar: For å få denne rullegardinmenyen med flere valg i andre celler, må du endre VBA-koden i backend. Anta at du vil få dette for C2, C3 og C4, du må erstatte følgende linje i koden: If Target.Address = "$ C $ 2" Så med denne linjen: If Target.Address = "$ C $ 2" Eller Target.Address = "$ C $ 3" Eller Target.Address = "$ C $ 4" Deretter
Spørsmål: Jeg må opprette flere rullegardinmenyer i hele kolonnen 'C'. Hvordan får jeg dette til alle cellene i kolonnene med multi-select funksjonalitet? Svar: For å aktivere flere valg i rullegardinmenyer i en hel kolonne, erstatter du følgende linje i koden: If Target.Address = "$ C $ 2" Så med denne linjen: Hvis Target.Column = 3 Deretter På lignende linjer, hvis du vil ha denne funksjonaliteten i kolonne C og D, bruker du linjen nedenfor: Hvis Target.Column = 3 eller Target.Column = 4 Deretter
Spørsmål: Jeg må opprette flere rullegardinmenyer på rad. Hvordan kan jeg gjøre dette? Svar: Hvis du trenger å lage rullegardinlister med flere valg på rad (la oss si den andre raden), må du erstatte kodelinjen nedenfor: Hvis Target.Address = "$ C $ 2" Så med denne linjen: Hvis Target.Row = 2 Så på samme måte, hvis du vil at dette skal fungere for flere rader (la oss si andre og tredje rad), bruker du koden nedenfor: I stedet Target.Row = 2 eller Target.Row = 3
Spørsmål: Fra nå av er flere valg atskilt med et komma. Hvordan kan jeg endre dette for å skille disse med mellomrom (eller annen separator). Svar: For å skille disse med en annen separator enn komma, må du erstatte følgende linje med VBA -kode: Target.Value = Oldvalue & "," & Newvalue med denne linjen med VBA -kode: Target.Value = Oldvalue & "" & Newvalue På samme måte, hvis du vil endre komma med et annet tegn, for eksempel |, kan du bruke følgende kodelinje: Target.Value = Oldvalue & "|" & Newvalue
Spørsmål: Kan jeg få hvert utvalg på en egen linje i samme celle? Svar: Ja du kan. For å få dette må du erstatte linjen nedenfor med VBA -kode: Target.Value = Oldvalue & "," & Newvalue med denne kodelinjen: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine setter inn en ny linje i samme celle . Så når du velger fra rullegardinmenyen, blir den satt inn i en ny linje.
Spørsmål: Kan jeg få flervalgsfunksjonaliteten til å fungere i et beskyttet ark? Svar: Ja du kan. For å få dette gjort må du gjøre to ting: Legg til følgende linje i koden (rett etter DIM -setningen): Me.Protect UserInterfaceOnly: = True For det andre må du sørge for at cellene - som har rullegardinmenyen med flere valgfunksjoner - ikke er låst når du beskytter hele arket. Her er en opplæring om hvordan du gjør dette: Lås celler i Excel
