Jeden z mých kolegů se mě zeptal, zda je možné provést více výběrů v rozevíracím seznamu v aplikaci Excel.
Když vytvoříte rozevírací seznam, můžete provést pouze jeden výběr. Pokud vyberete jinou položku, bude první nahrazena novým výběrem.
Chtěl provést více výběrů ze stejného rozevíracího seznamu takovým způsobem, aby se výběry přidaly k již existující hodnotě v buňce.
Něco, jak je uvedeno níže na obrázku:
S integrovanými funkcemi Excelu to nemůžete udělat.
Jediným způsobem je použít kód VBA, který se spustí vždy, když provedete výběr, a přidá vybranou hodnotu ke stávající hodnotě.
Podívejte se na video - Jak vybrat více položek z rozevíracího seznamu aplikace Excel
Jak provést více výběrů v rozevíracím seznamu
V tomto tutoriálu vám ukážu, jak provést více výběrů v rozevíracím seznamu aplikace Excel (s opakováním a bez opakování).
Toto byl jeden z nejpopulárnějších tutoriálů k Excelu na tomto webu. Protože dostávám spoustu podobných otázek, rozhodl jsem se na konci tohoto tutoriálu vytvořit sekci FAQ. Pokud tedy máte po přečtení tohoto článku nějaké dotazy, podívejte se nejprve do sekce FAQ.Rozevírací seznam, který umožňuje více výběrů, má dvě části:
- Vytvoření rozevíracího seznamu.
- Přidání kódu VBA na back-end.
Vytvoření rozevíracího seznamu v aplikaci Excel
Zde jsou kroky k vytvoření rozevíracího seznamu v aplikaci Excel:
- Vyberte buňku nebo oblast buněk, kde se má rozevírací seznam zobrazovat (v tomto případě C2).
- Přejděte na Data -> Datové nástroje -> Ověření dat.
- V dialogovém okně Ověření dat na kartě nastavení vyberte jako ověřovací kritéria „Seznam“.
- V poli Zdroj vyberte buňky, které mají v rozevíracím seznamu požadované položky.
- Klikněte na OK.
Buňka C2 má nyní rozevírací seznam, který zobrazuje názvy položek v A2: A6.
V současné době máme rozevírací seznam, kde můžete vybrat jednu položku najednou (jak je uvedeno níže).
Abychom tuto rozbalovací nabídku povolili, abychom mohli provádět více výběrů, musíme na zadní konec přidat kód VBA.
Následující dvě části tohoto kurzu vám poskytnou kód VBA, který umožní více výběrů v rozevíracím seznamu (s opakováním i bez něj).
Kód VBA umožňující více výběrů v rozevíracím seznamu (s opakováním)
Níže je uveden kód Excel VBA, který nám umožní vybrat více než jednu položku z rozevíracího seznamu (což umožňuje opakování při výběru):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' To make mutpleple selections in a Drop Down List in Excel Dim Oldvalue as String Dim Newvalue as String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Pak pokud Target.SpecialCells (xlCellTypeAllValidation) není nic, pak 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
Nyní musíte tento kód umístit do modulu v editoru VB (jak je uvedeno níže v části „Kam umístit kód VBA“).
Když umístíte tento kód do backendu (popsaného dále v tomto kurzu), umožní vám to provést více výběrů v rozevíracím seznamu (jak je uvedeno níže).
Upozorňujeme, že pokud vyberete položku více než jednou, bude znovu zadána (opakování je povoleno).
Vyzkoušejte sami … Stáhněte si ukázkový soubor
Kód VBA umožňující více výběrů v rozevíracím seznamu (bez opakování)
Mnoho lidí se ptá na kód pro výběr více položek z rozevíracího seznamu bez opakování.
Zde je kód, který zajistí, že položku lze vybrat pouze jednou, aby nedocházelo k žádnému opakování:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' To allow multiple selections in a Drop Down List in Excel (without repetition) 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
Nyní musíte umístit tento kód do modulu v editoru VB (jak ukazuje následující část tohoto kurzu).
Tento kód vám umožní vybrat více položek z rozevíracího seznamu. Položku však budete moci vybrat pouze jednou. Pokud to zkusíte znovu vybrat, nic se nestane (jak je uvedeno níže).
Vyzkoušejte sami … Stáhněte si ukázkový soubor
Kam vložit kód VBA
Než začnete používat tento kód v aplikaci Excel, musíte jej umístit do back-endu, aby se spustil při jakékoli změně v rozevíracím výběru.
Při vkládání kódu VBA do backendu aplikace Excel postupujte podle následujících pokynů:
- Přejděte na kartu Vývojář a klikněte na Visual Basic (můžete také použít klávesovou zkratku - Alt + F11). Tím se otevře editor jazyka Visual Basic.
- Vlevo by mělo být podokno Průzkumníka projektu (pokud tam není, použijte Control + R, aby bylo viditelné).
- Poklepejte na název listu (v levém podokně), kde je umístěn rozevírací seznam. Tím se otevře okno kódu pro tento list.
- V okně kódu zkopírujte a vložte výše uvedený kód.
- Zavřete editor VB.
Když se nyní vrátíte do rozevíracího seznamu a provedete výběry, umožní vám to provést více výběrů (jak je uvedeno níže):
Vyzkoušejte sami … Stáhněte si ukázkový soubor
Poznámka: Protože k tomu používáme kód VBA, je třeba sešit uložit s příponou .xls nebo .xlsm.
Často kladené otázky (FAQ)
Vytvořil jsem tuto sekci, abych odpověděl na některé z nejčastějších dotazů ohledně tohoto kurzu a kódu VBA. Pokud máte nějaké dotazy, žádám vás, abyste si nejprve prošli tento seznam dotazů.
Otázka: V kódu VBA je funkce pouze pro buňku C2. Jak to získám pro jiné buňky? Odpověď: Chcete-li získat tento rozevírací seznam více výběrů v jiných buňkách, musíte upravit kód VBA v backendu. Předpokládejme, že to chcete získat pro C2, C3 a C4, musíte v kódu nahradit následující řádek: If Target.Address = "$ C $ 2" Then with this line: If Target.Address = "$ C $ 2" Nebo Target.Address = "$ C $ 3" Nebo Target.Address = "$ C $ 4" Potom
Otázka: Potřebuji vytvořit více rozevíracích seznamů v celém sloupci „C“. Jak to získám pro všechny buňky ve sloupcích s funkcí vícenásobného výběru? Odpověď: Chcete-li povolit více výběrů v rozevíracích seznamech v celém sloupci, nahraďte v kódu následující řádek: If Target.Address = "$ C $ 2" Then s tímto řádkem: If Target.Column = 3 Then Na podobných řádcích, pokud chcete tuto funkci ve sloupci C a D, použijte následující řádek: Pokud Target.Column = 3 nebo Target.Column = 4 Then
Otázka: Potřebuji vytvořit více rozevíracích seznamů za sebou. Jak to mohu udělat? Odpověď: Pokud potřebujete vytvořit rozevírací seznamy s více výběry za sebou (řekněme druhý řádek), musíte nahradit následující řádek kódu: If Target.Address = "$ C $ 2" Then with this line: If Target.Row = 2 Then Podobně, pokud chcete, aby to fungovalo pro více řádků (řekněme druhý a třetí řádek), použijte místo toho následující řádek kódu: If Target.Row = 2 or Target.Row = 3 Then
Otázka: V tuto chvíli jsou více výběrů odděleny čárkou. Jak to mohu změnit, abych je oddělil mezerou (nebo jiným oddělovačem). Odpověď: Chcete -li je oddělit oddělovačem jiným než čárkou, musíte nahradit následující řádek kódu VBA: Target.Value = Oldvalue & "," & Newvalue tímto řádkem kódu VBA: Target.Value = Oldvalue & "" & Newvalue Podobně, pokud chcete změnit čárku jiným znakem, například |, můžete použít následující řádek kódu: Target.Value = Oldvalue & "|" & Newvalue
Otázka: Mohu získat každý výběr na samostatném řádku ve stejné buňce? Odpověď: Ano, můžete. Abyste toho dosáhli, musíte nahradit níže uvedený řádek kódu VBA: Target.Value = Oldvalue & "," & Newvalue tímto řádkem kódu: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine vloží nový řádek do stejné buňky . Kdykoli tedy provedete výběr z rozevíracího seznamu, bude vložen do nového řádku.
Otázka: Mohu zajistit, aby funkce vícenásobného výběru fungovala na chráněném listu? Odpověď: Ano, můžete. Abyste to zvládli, musíte udělat dvě věci: Přidejte do kódu následující řádek (hned za příkaz DIM): Me.Protect UserInterfaceOnly: = True Za druhé, musíte zajistit, aby buňky - které mají rozevírací seznam s funkcí vícenásobného výběru - nebyly při ochraně celého listu uzamčeny. Zde je návod, jak na to: Uzamknout buňky v Excelu
