Jeden z mojich kolegov sa ma spýtal, či je možné vykonať viacero výberov v rozbaľovacom zozname v programe Excel.
Keď vytvoríte rozbaľovací zoznam, môžete vykonať iba jeden výber. Ak vyberiete inú položku, prvá bude nahradená novým výberom.
Chcel vykonať viacero výberov z tej istej rozbaľovacej ponuky takým spôsobom, aby sa výbery pridali k už prítomnej hodnote v bunke.
Niečo, ako je uvedené nižšie na obrázku:
S integrovanými funkciami Excelu to nemôžete urobiť.
Jediným spôsobom je použiť kód VBA, ktorý sa spustí vždy, keď urobíte výber, a pridá vybratú hodnotu k existujúcej hodnote.
Pozrite si video - Ako vybrať viacero položiek z rozbaľovacieho zoznamu programu Excel
Ako vykonať viac výberov v rozbaľovacom zozname
V tomto tutoriále vám ukážem, ako vykonať viacero výberov v rozbaľovacom zozname programu Excel (s opakovaním a bez opakovania).
Toto bol jeden z najobľúbenejších tutoriálov pre Excel na tomto webe. Keďže dostávam veľa podobných otázok, rozhodol som sa na konci tohto tutoriálu vytvoriť časť Časté otázky. Ak teda máte po prečítaní otázky nejaké otázky, najskôr si prečítajte sekciu Časté otázky.Rozbaľovací zoznam, ktorý umožňuje viacnásobný výber, má dve časti:
- Vytvorenie rozbaľovacieho zoznamu.
- Pridanie kódu VBA na back-end.
Vytvorenie rozbaľovacieho zoznamu v programe Excel
Tu je postup, ako vytvoriť rozbaľovací zoznam v programe Excel:
- Vyberte bunku alebo rozsah buniek, v ktorých sa má rozbaľovací zoznam zobrazovať (v tomto prípade C2).
- Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
- V dialógovom okne Overenie údajov na karte nastavení vyberte ako kritériá overenia položku „Zoznam“.
- V poli Zdroj vyberte bunky, ktoré majú v rozbaľovacom zozname požadované položky.
- Kliknite na tlačidlo OK.
Bunka C2 má teraz rozbaľovací zoznam, ktorý zobrazuje názvy položiek v A2: A6.
Odteraz máme rozbaľovací zoznam, v ktorom môžete vybrať jednu položku naraz (ako je uvedené nižšie).
Aby sme umožnili tento rozbaľovací zoznam, aby sme mohli vykonávať viac výberov, musíme na zadný koniec pridať kód VBA.
Nasledujúce dve časti tohto tutoriálu vám poskytnú kód VBA, ktorý umožní viacnásobný výber v rozbaľovacom zozname (s opakovaním aj bez neho).
Kód VBA umožňujúci viacnásobný výber v rozbaľovacom zozname (s opakovaním)
Nasleduje kód Excel VBA, ktorý nám umožní vybrať viac ako jednu položku z rozbaľovacieho zoznamu (čo umožňuje opakovanie pri výbere):
Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah) „Kód od Sumit Bansal z https://trumpexcel.com“ Na vytvorenie viacerých výberov v rozbaľovacom zozname v programe Excel Dim Oldvalue ako reťazec Dim Newvalue ako reťazec pri chybe GoTo Exitsub If Target.Address = "$ C $ 2" Potom ak Target.SpecialCells (xlCellTypeAllValidation) nie je nič, potom GoTo Exitsub Else: If Target.Value = "" Potom 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
Teraz musíte tento kód vložiť do modulu v editore VB (ako je uvedené nižšie v časti „Kam umiestniť kód VBA“).
Keď umiestnite tento kód do backendu (popísaný ďalej v tomto návode), umožní vám to urobiť viac výberov v rozbaľovacom zozname (ako je uvedené nižšie).
Upozorňujeme, že ak vyberiete položku viackrát, bude znova zadaná (opakovanie je povolené).
Skúste to sami … Stiahnite si ukážkový súbor
Kód VBA umožňujúci viacnásobný výber v rozbaľovacom zozname (bez opakovania)
Mnoho ľudí sa pýtalo na kód na výber viacerých položiek z rozbaľovacieho zoznamu bez opakovania.
Tu je kód, ktorý zaistí, že položku je možné vybrať iba raz, aby sa neopakovali:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Umožniť viacnásobný výber v rozbaľovacom zozname v programe Excel (bez opakovania) 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. Hodnota 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
Teraz musíte tento kód vložiť do modulu v editore VB (ako je uvedené v ďalšej časti tohto tutoriálu).
Tento kód vám umožní vybrať viac položiek z rozbaľovacieho zoznamu. Položku však budete môcť vybrať iba raz. Ak to skúsite znova vybrať, nič sa nestane (ako je uvedené nižšie).
Skúste to sami … Stiahnite si ukážkový súbor
Kam vložiť kód VBA
Predtým, ako začnete používať tento kód v Exceli, musíte ho vložiť do back-endu, aby sa spustil vždy, keď dôjde k akejkoľvek zmene v rozbaľovacom výbere.
Pri vkladaní kódu VBA na serverové jadro programu Excel postupujte podľa nasledujúcich pokynov:
- Prejdite na kartu Vývojár a kliknite na položku Visual Basic (môžete tiež použiť klávesovú skratku - Alt + F11). Tým sa otvorí editor jazyka Visual Basic.
- Vľavo by mala byť tabla Project Explorer (ak tam nie je, použite Ctrl + R na jej zviditeľnenie).
- Dvakrát kliknite na názov pracovného hárka (na ľavej table), kde sa nachádza rozbaľovací zoznam. Tým sa otvorí okno s kódom pre tento pracovný hárok.
- V okne kódu skopírujte a prilepte vyššie uvedený kód.
- Zatvorte editor VB.
Keď sa teraz vrátite do rozbaľovacej ponuky a vyberiete možnosti, umožní vám to vykonať viac výberov (ako je uvedené nižšie):
Skúste to sami … Stiahnite si ukážkový súbor
Poznámka: Pretože na to používame kód VBA, zošit musíte uložiť s príponou .xls alebo .xlsm.
Často kladené otázky (FAQ)
Túto sekciu som vytvoril, aby som zodpovedal na niektoré z najčastejších otázok týkajúcich sa tohto tutoriálu a kódu VBA. Ak máte akékoľvek otázky, prosím, najskôr si prečítajte tento zoznam otázok.
Otázka: V kóde VBA je funkcia iba pre bunku C2. Ako to získam pre ostatné bunky? Odpoveď: Ak chcete získať tento rozbaľovací zoznam s viacnásobným výberom v iných bunkách, musíte upraviť kód VBA v serverovom serveri. Predpokladajme, že to chcete získať pre C2, C3 a C4, musíte v kóde nahradiť nasledujúci riadok: If Target.Address = "$ C $ 2" Potom s týmto riadkom: If Target.Address = "$ C $ 2" Alebo Target.Address = "$ C $ 3" Alebo Target.Address = "$ C $ 4" Potom
Otázka: Potrebujem vytvoriť viac rozbaľovacích zoznamov v celom stĺpci „C“. Ako to získam pre všetky bunky v stĺpcoch s funkciou viacnásobného výberu? Odpoveď: Ak chcete povoliť viacnásobný výber v rozbaľovacích ponukách v celom stĺpci, nahraďte nasledujúci kód v kóde: If Target.Address = "$ C $ 2" Then this line: If Target.Column = 3 Then Na podobných riadkoch, if chcete túto funkciu v stĺpci C a D, použite nasledujúci riadok: Ak Target.Column = 3 alebo Target.Column = 4 Then
Otázka: Potrebujem vytvoriť viacero rozbaľovacích zoznamov za sebou. Ako to môžem urobiť? Odpoveď: Ak potrebujete vytvoriť rozbaľovacie zoznamy s viacerými výbermi za sebou (povedzme v druhom riadku), musíte nahradiť nasledujúci riadok kódu: If Target.Address = "$ C $ 2" Potom s týmto riadkom: Ak Target.Row = 2 Potom Podobne Ak chcete, aby to fungovalo pre viac riadkov (povedzme druhý a tretí riadok), použite namiesto toho nasledujúci riadok kódu: Ak Target.Row = 2 alebo Target.Row = 3 Potom
Otázka: Odteraz sú viacnásobné výbery oddelené čiarkou. Ako to môžem zmeniť, aby som ich oddelil medzerou (alebo iným oddeľovačom). Odpoveď: Ak ich chcete oddeliť oddeľovačom iným ako čiarkou, musíte nahradiť nasledujúci riadok kódu VBA: Target.Value = Oldvalue & "," & Newvalue týmto riadkom kódu VBA: Target.Value = Oldvalue & "" & Newvalue Podobne, ak chcete zmeniť čiarku na iný znak, ako napríklad |, môžete použiť nasledujúci riadok kódu: Target.Value = Oldvalue & "|" & Newvalue
Otázka: Môžem dostať každý výber do samostatného riadka v tej istej bunke? Odpoveď: Áno, môžete. Aby ste to dosiahli, musíte nahradiť nasledujúci riadok kódu VBA: Target.Value = Oldvalue & "," & Newvalue týmto riadkom kódu: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine vloží nový riadok do tej istej bunky . Kedykoľvek teda vyberiete z rozbaľovacej ponuky, vloží sa do nového riadka.
Otázka: Môžem nechať funkciu viacnásobného výberu fungovať na chránenom hárku? Odpoveď: Áno, môžete. Aby ste to dosiahli, musíte urobiť dve veci: Do kódu pridajte nasledujúci riadok (hneď za príkaz DIM): Me.Protect UserInterfaceOnly: = Pravda Za druhé, musíte zabezpečiť, aby bunky - ktoré majú rozbaľovaciu ponuku s funkciou viacnásobného výberu - neboli uzamknuté, keď chránite celý hárok. Tu je návod, ako to urobiť: Uzamknutie buniek v programe Excel
