Rozevírací seznamy v Excelu VBA: jednoduchý průvodce s příklady

Rozevírací seznamy jsou jedním z nejužitečnějších nástrojů v Excelu VBA, které uživatelům umožňují vybrat hodnoty z předdefinovaných seznamů. Usnadňují zadávání dat, snižují pravděpodobnost chyb a zvyšují efektivitu práce s tabulkami. V tomto gajdu se podíváme na základy použití rozevíracích seznamů v Excelu VBA a uvedeme příklady jejich vytváření a přizpůsobení.

Prvním krokem k vytvoření rozevíracího seznamu je určit rozsah buněk, ze kterých budou hodnoty seznamu odebrány. K tomu se používá metoda DataValidation objektu Range. Pro uvedený rozsah je pak nastaven datový typ rovný xlvalidatelist, aby se stal rozevíracím seznamem. Dále je třeba zadat zdroj dat pro seznam, což je obvykle rozsah buněk obsahujících hodnoty seznamu.

Příklad vytvoření rozevíracího seznamu:

Sub CreateDropDownList()


Dim rng As Range


Set rng = Range("A1:A5")


With rng.Validation


.Delete


.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$B$1:$B$3"


.IgnoreBlank = True


.InCellDropdown = True


.InputTitle = "vyberte hodnotu ze seznamu"


.ErrorTitle = "Neplatná hodnota"


.InputMessage = " vyberte prosím hodnotu ze seznamu."


.Erroormessage = " vyberte prosím hodnotu z poskytnutého seznamu."


.ShowInput = True


.ShowError = True


End With


End Sub

V uvedeném příkladu vytvoříme rozevírací seznam v rozsahu buněk A1: A5. Zdrojem dat pro seznam je rozsah buněk B1: B3. Kód také obsahuje některé další možnosti, jako je styl chybových zpráv a výzev pro uživatele.

Definice rozevíracího seznamu

Rozbalovací seznam lze vytvořit pomocí objektu ComboBox nebo listboxu. Hlavní rozdíl mezi nimi je v tom, že ComboBox vám umožňuje vybrat pouze jednu možnost a ListBox vám umožňuje vybrat více možností.

Rozbalovací seznam může být užitečný v mnoha programovacích situacích, například:

• Vstup údajů: uživatel si může vybrat jednu z poskytnutých možností pro zadávání dat.
• Filtrování dat: rozevírací seznamy lze použít k filtrování dat na základě zvolené hodnoty.
• Správa parametrů: rozbalovací seznamy lze použít k výběru možností, které ovlivňují fungování programu.

V Excelu VBA lze rozbalovací seznam vytvořit programově pomocí příslušných metod a vlastností objektů ComboBox nebo ListBox. Můžete také upravit vzhled seznamu, styl a dokonce přidat události, které zpracovávají výběr uživatele.

Výhody použití rozevíracích seznamů v Excelu VBA

1. Zlepšení uživatelské zkušenosti.

Rozevírací seznamy vám umožňují zjednodušit zadávání dat a usnadnit práci s tabulkami v Excelu. Místo toho, aby uživatel zadával data ručně, může ze seznamu vybrat hodnotu. To snižuje možné chyby a zjednodušuje proces práce.

2. Omezení výběru.

Rozevírací seznamy umožňují uživateli omezit výběr pouze na určité hodnoty. To je užitečné zejména v případě tabulek, které obsahují určité možnosti výběru. Například v seznamu zemí lze omezit výběr pouze na země z určitého seznamu a vyloučit možné překlepy nebo nesprávné vstupy.

3. Pohodlná aktualizace dat.

Použití rozevíracích seznamů usnadňuje aktualizaci dat v tabulkách. Pokud se seznam hodnot změnil, stačí jej jednou změnit a všechny rozbalovací seznamy související s tímto seznamem budou automaticky aktualizovány. To šetří čas a usnadňuje proces podpory tabulek.

4. Možnost Validace dat.

Rozbalovací seznamy lze také použít k validaci dat. Můžete například nastavit pravidlo, aby si uživatel mohl vybrat pouze hodnoty z rozevíracího seznamu a všechny ostatní hodnoty budou považovány za neplatné. To umožňuje zajistit správnost a integritu dat v tabulce.

5. Zlepšení vizuálního zobrazení dat.

Rozbalovací seznamy lze použít ke zlepšení vizuálního zobrazení dat. Můžete například použít ikony nebo barvy k označení určitých hodnot v seznamu. Díky tomu jsou tabulky přehlednější a přehlednější pro uživatele.

Obecně platí, že použití rozevíracích seznamů v Excelu VBA vám umožňuje zjednodušit zadávání dat, zlepšit uživatelskou zkušenost a usnadnit práci s tabulkami. Poskytují také validaci dat a zlepšují vizuální reprezentaci dat. Je to užitečný nástroj pro vývojáře VBA, který pomáhá vytvářet pohodlnější a efektivnější aplikace Excel.

Vytvoření rozevíracího seznamu v Excelu VBA

V Excelu VBA lze vytvořit rozevírací seznam, který uživateli umožní vybrat si jednu z předdefinovaných hodnot. To je velmi užitečné při navrhování Maker, kdy je nutné uživateli poskytnout omezenou sadu možností, které si může vybrat.

K vytvoření rozevíracího seznamu v Excelu VBA můžeme použít objekt "ComboBox". Tento objekt nám umožňuje vytvořit vstupní pole, po kliknutí na které se otevře seznam dostupných možností.

Proces vytváření rozevíracího seznamu v Excelu VBA se skládá z několika kroků:

Krok 1:	Otevřete Visual Basic Editor stisknutím klávesy "ALT + F11".
Krok 2:	Vložte nový modul kliknutím na pravé tlačítko myši na " Insert "a výběrem"Module".
Krok 3:	Do modulu vložte následující kód:

Sub CreateDropDownList()Dim ws As WorksheetDim rng As RangeDim cbo As ComboBoxSet ws = ThisWorkbook.Worksheets("Sheet1")Set rng = ws.Range("A1:A5") 'Замените диапазон на свойSet cbo = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1").ObjectWith cbo.Left = rng.Left.Top = rng.Top.Width = rng.Width.ListFillRange = rng.Address.LinkedCell = ws.Range("B1").Address 'Замените на свою ячейкуEnd WithEnd Sub

V tomto kódu vytváříme objekty "Worksheet " a" Range", abychom určili, v jakém listu a v jakém rozsahu chceme vytvořit rozevírací seznam. Poté použijeme metodu " Add "objektu" OLEObjects "k přidání objektu" ComboBox " na náš list. Nastavujeme také různé vlastnosti tohoto objektu, jako je pozice a velikost, seznam dostupných hodnot a buňka, která bude spojena s tímto seznamem.

Chcete-li spustit tento kód a vytvořit rozevírací seznam, můžete zavřít Visual Basic Editor a postupovat podle následujících kroků v Excelu:

1. Vyberte Developer kartu v horní liště.
2. Klikněte na Insert a poté vyberte tlačítko "Macros".
3. Vyberte v seznamu Makro createdropdownlist a klikněte na tlačítko"Spustit".

Po provedení těchto kroků budete mít v buňce "B1" vytvořen rozevírací seznam, který bude obsahovat hodnoty z rozsahu "A1:A5". Uživatel si bude moci vybrat jednu z těchto hodnot kliknutím na vstupní pole.

Rozbalovací seznamy v Excelu VBA mohou být velmi užitečné při vytváření uživatelských rozhraní a poskytují snadný způsob, jak omezit možnosti výběru uživatelů. Mohou být také nakonfigurovány pomocí složitějšího kódu pro pokročilejší funkce, jako je automatické vyplňování nebo filtrování hodnot.

Krok 1: Vytvoření seznamu

Před vytvořením rozevíracího seznamu v Excelu VBA je nutné vytvořit rozsah hodnot, ze kterého bude položka vybrána. Tento rozsah může být umístěn na stejném listu, kde je rozbalovací seznam, nebo na jiném listu ve stejném souboru.

Chcete-li vytvořit seznamový rozsah v Excelu VBA, použijte následující postup:

Sub Create_List_Range()
Dim rng As Range
Set rng = Worksheets ("List1").Range("A1:A5")
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='List1'!$A$1:$A$5"
End With
End Sub

V tomto příkladu se vytvoří rozsah hodnot od buňky A1 do A5 na listu List1. Na tento rozsah se pak vztahuje validace, která omezuje možnost výběru pouze z tohoto rozsahu (Typ xlValidateList). Vzorec1 určuje rozsah hodnot seznamu.

V případě potřeby změňte rozsah položky seznamu, změňte rozsah v kódu a restartujte postup.

Krok 2: Přidání rozevíracího seznamu na list

K přidání rozevíracího seznamu použijeme metodu DataValidation. Tato metoda umožňuje nastavit limity na hodnoty, které lze zadat do buněk. V našem případě nastavíme limit, aby buňka mohla zadat pouze jednu z hodnot, které budou uvedeny v rozevíracím seznamu.

Nejprve určíme rozsah buněk, ve kterých bude rozbalovací seznam umístěn. K tomu lze použít vlastnost Range. Rozsah lze například specifikovat tímto způsobem:

Dim rng As RangeSet rng = ThisWorkbook.Worksheets("Название листа").Range("A1:A10")

V tomto případě uvádíme rozsah od buňky A1 po buňku A10 na našem listu.

Dále pomocí metody DataValidation, stanovujeme limit pro tento rozsah. Můžete například nastavit limit na seznam hodnot:

With rng.Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Значение1, Значение2, Значение3"End With

V tomto případě nastavíme limit typu "seznam" (xlValidateList) na rozsah rng a v parametru Formula1 zadáme seznam hodnot přes čárku.

Po provedení těchto kroků bude rozbalovací seznam přidán na vybraný list a určitý rozsah buněk bude omezen na hodnoty ze seznamu.

Ve druhém kroku tedy nastavíme limit na rozsah buněk pomocí metody DataValidation a určíme seznam hodnot, které budou použity v rozevíracím seznamu.

Přidání funkce rozevíracího seznamu

K realizaci této funkce je nutné použít událost Worksheet_Change . Tato událost je způsobena jakoukoli změnou dat na listu a umožňuje provádět určité akce v závislosti na typu změny.

Pro příklad zvažte situaci, kdy v buňce A1 máme nastavený seznam možných hodnot a v buňce B1 chceme mít rozevírací seznam, který se automaticky aktualizuje, když se změní seznam v buňce A1.

V kódu VBA pro list, na kterém jsou tyto buňky umístěny, je třeba přidat následující obslužnou rutinu události:

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("A1")) Is Nothing ThenApplication.EnableEvents = FalseRange("B1").ClearContentsWith Range("B1").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='" & ActiveSheet.Name & "'!$A$1:$A$" & Range("A" & Rows.Count).End(xlUp).Row.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = False.ShowError = FalseEnd WithApplication.EnableEvents = TrueEnd IfEnd Sub

Všimněte si , že obsluha události používá objekt Target, což je rozsah buněk, ve kterých byly provedeny změny. Pomocí funkce Intersect je ověřeno, že změny se týkají pouze buňky A1. Pokud ano, buňka B1 nejprve odstraní předchozí rozevírací seznam a poté přidá nový seznam založený na hodnotě buňky A1.

V tomto případě jsme použili validaci buňky B1 typu xlValidateList . Parametr Formula1 uvádí hodnoty seznamu, které pocházejí z listu, na kterém je kód VBA.

Nyní se při změně seznamu v buňce A1 automaticky aktualizuje rozbalovací seznam v buňce B1. To vám umožní rychle a pohodlně vybrat hodnoty z aktualizovaného seznamu, aniž byste museli seznam znovu upravovat.