Mai alkalommal – mint a cím is sejteti – a kereshető legördülő listáról (más néven adatérvényesítési listáról, angolul searchable drop-down list) lesz szó, mely automatikusan bővül, ha a forrást változtatod.
Az egyik titkos összetevő a Táblázat formátum, a másik lényeges dolog pedig, hogy milyen verziójú Excelt (Office-t) használsz.
Két módszert ismertetek:
I. VBA-val, mely régebbi verzióknál is működik és gépelés közben adja meg a találatokat. Animáció:
II. Dinamikus tömbképletekkel, melyek kizárólag a Microsoft 365-ben (régebbi és szerintem még jó darabig közismertebb nevén: Office 365-ben) érhetők el. A dinamikus tömbképletekről további infó itt. Itt a begépelt szövegrész után a legördülőhöz tartozó nyílra kell kattintani a találatokért. Animáció:
Nézzük a két megoldást:
I. VBA-val:
1. a legördülő lista forrását alakítsuk át táblázattá: Beszúrás menü -> Táblázat.
A táblázat neve itt “OrszagTabla”.
2. majd adjunk hozzá a munkalaphoz egy ActiveX-es Beviteli Lista (Combo Box) elemet:
Fejlesztöeszközök menü -> Beszúrás-> Beviteli lista (ActiveX-vezérlő)
Ha a Fejlesztöeszközök menü nem látszik: lásd az alábbi videómat 0:49-től 1:16-ig:
3. Jobb klikk a beszúrt elemen -> Tulajdonságok
4. a “MatchEntry” résznél válasszuk ki a “2 – fmMatchEntryNone”-t (itt már angolul jelennek meg a lehetőségek) -> bezárás piros X-szel
5. ALT + F11 (VBA szerkesztő) -> keressük meg a legördülőt tartalmazó munkalap nevét és dupla klikk:
6. a jobb oldali részre másoljuk be az alábbi eseménykezeléses makrókat:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
Option Explicit Dim Cella As Range Dim Rng_SourceVariant As Variant 'must be a variant because of ComboBox1.List 'készítette: Tulner Roland (xlmotyo.hu) 'kereshetõ legördülõ lista, mely régebbi Excel verziókban is mûködik (nem csak Office 365-ben) ' a lista automatikusan bõvül, mivel a forrása táblázat formátumú 'Inspiráció: _ https://www.mrexcel.com/board/threads/autocomplete-drop-down-list-autofill-drop-down-list-solution-found.987505/ Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Rng_DropDown As Range Set Rng_DropDown = LegorduloLista.Range("A2:A" & LegorduloLista.UsedRange.Rows.Count) 'Rng_SourceVariant = Forras.Range("A2:A" & Forras.UsedRange.Rows.Count) 'variant típus! Rng_SourceVariant = Forras.ListObjects("OrszagTabla").DataBodyRange.Value 'variant típus! If Not Intersect(Rng_DropDown, Target) Is Nothing And Target.Count = 1 Then Me.ComboBox1.List = Rng_SourceVariant Me.ComboBox1.Height = Target.Height + 3 Me.ComboBox1.Width = Target.Width Me.ComboBox1.Top = Target.Top Me.ComboBox1.Left = Target.Left Me.ComboBox1 = Target Me.ComboBox1.Visible = True Me.ComboBox1.Activate Set Cella = Target Else Me.ComboBox1.Visible = False End If End Sub Private Sub ComboBox1_Change() Dim D As Object, Elem, Talalat As String If Me.ComboBox1 <> "" And IsError(Application.Match(Me.ComboBox1, Rng_SourceVariant, 0)) Then Set D = CreateObject("scripting.dictionary") Talalat = "*" & UCase(Me.ComboBox1) & "*" '!!!!!!!!!! For Each Elem In Rng_SourceVariant If Trim(UCase(Elem)) Like Trim(Talalat) Then D(Elem) = "" '!!!!!!!!! Next Elem Me.ComboBox1.List = D.keys Me.ComboBox1.DropDown End If ActiveCell.Value = Me.ComboBox1 End Sub Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Me.ComboBox1.List = Rng_SourceVariant Me.ComboBox1.DropDown End Sub Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then '13: Enter - "KeyPress" eseménnyel nem mûködik If IsError(Application.Match(ActiveCell, Rng_SourceVariant, 0)) Then ActiveCell = Cella ActiveCell.Offset(1, 0).Select ElseIf KeyCode = 9 Then '9: TAB If IsError(Application.Match(ActiveCell, Rng_SourceVariant, 0)) Then ActiveCell = Cella ActiveCell.Offset(0, 1).Select End If End Sub |
Néhány komment a kódhoz:
- a Worksheet_SelectionChange-es esemény makró hozzáadja a ComboBox-ot a kijelölt cellához az A1:A8-as tartományon belül
- a ComboBox-nál három eseménykezelős kódot láthatunk: változás (Change), dupla klikk (DblClick) és billentyűlenyomás (KeyDown) esetére
- az eseménykezeléses makrók automatikusan lefutnak ha az esemény bekövetkezik, vagyis nem kell külön futtatni őket. Menjünk a munkalapra és gépeljünk be pár karaktert, mint a fenti animáción éééés működik 🙂
Végezetül adjunk hozzá egy sort a forrás táblázathoz és ellenőrizzük, megjelenik-e az új tétel a legördülőben, méghozzá a végén.
II. Dinamikus tömbképletekkel (csak Office 365-ben):
1. a legördülő lista forrását itt is alakítsuk át táblázattá: Beszúrás menü -> Táblázat. A táblázat neve: “OrszagTabla”.
2. a legördülő lapján adjuk hozzá a dinamikus tömbképletet a forrás táblázatot használva: itt a C2-es cellába az alábbi képlet került:
=TISZTÍT(EGYEDI(SORBA.RENDEZ(SZŰRŐ(OrszagTabla[European countries];SZÁM(SZÖVEG.KERES(LegorduloLista!A2;OrszagTabla[European countries];1))))))
Tömbképlet eredménye: a kék szegély jelzi Excelben, hogy iyen képlettel van dolgunk:
3. A2-es cella kijelölve: Adatérvényesítés (lista) hozzáadása: forrása a dinamikus tömb, melyre “#”-tel hivatkozunk:
Adatok menü -> Érvényesítés -> Megengedve: Lista -> Forrás: =$C$2# -> OK
4. Adatérvényesítésnél: Hibajelzés fül -> vegyük ki a pipát az “Érvénytelen adat beírásakor hibaüzenet jelenjen meg” elől -> OK
5. Próáljuk ki! Írjuk be az A2-es cellába pl.: “do”, majd a legördülőből válasszuk ki a releváns listából a megfelelőt:
Azonban ez a makrómentes trükk csak egy (ami itt az A2-es) cellában működik. Adjuk hozzá ezt az adatérvényesítést az A3-hoz (iménti 3. és 4. pont), ott már nem fog menni:
írjuk be az A3-ba pl.: “hu” és kattintsunk a legördülő nyilára: egy találat lesz, ami nem más, mint az A2-ben kiválasztott ország:
A dörzsöltebbek modhatnák: hát akkor kiveszem az érvényesítés képleténél a sor előtti $ jelet, hogy növekedjen a sorszám az A3-as, A4-es stb. cellákánál lévő adatérvényesítésnél. Vagyis az A2:A10-et kijelölöm, Adatérvényesítés, majd az érvényesítés képletét javítom erről:
$C$2#
erre:
=$C2#
Viszont ez esetben hiába kattintok a legördülős nyílra az A3-tól, nem kapom meg a listát. Összegezve: a $ törlése tömbképlet + adatérvényesítés kombónál nem járható.
6. Megoldásom: az A2:A10-es tartományban: attól függően, melyik cellát választjuk ki, írassuk ki a tömbképletet – mondjuk két cellával jobbra – és hivatkozzunk arra. Vagyis:
6/1: jelöljük ki az A2:A10-es tartományt
6/2: Adatok menü -> Érvényesítés -> Megengedve: Lista -> Forrás: =$C$2# -> OK
6/3: továbbra is az A2:A10 van kijelölve:
Adatérvényesítésnél: Hibajelzés fül -> vegyük ki a pipát az “Érvénytelen adat beírásakor hibaüzenet jelenjen meg” elől -> OK
6/4: ALT + F11 (VBA szerkesztő) -> keressük meg a legördülőt tartalmazó munkalap nevét és dupla klikk:
6/5: a jobb oldali részre másoljuk be az alábbi eseménykezeléses makrót:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Rng As Range 'készítette: Tulner Roland (xlmotyo.hu) Application.ScreenUpdating = False 'kereshetõ legördülõ listát add hozzá az "A" ("Ország") oszlophoz: Set Rng = Range("A2:A10") If Not Intersect(Rng, Target) Is Nothing And Target.Row <> 1 And Target.Cells.Count = 1 Then Columns("C").Clear Range("C" & Target.Row).Formula2R1C1 = "=CLEAN(UNIQUE(SORT(FILTER(OrszagTabla[European countries],ISNUMBER(SEARCH(LegorduloLista!RC[-2],OrszagTabla[European countries],1))))))" Rng.Validation.Delete With Target.Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$C$" & Target.Row & "#" .ShowError = False 'ugyanaz, mintha Adatérvényesítésnél kivennénk a pipát az "Érvénytelen adat beírásakor hibaüzenet jelenjen meg" elõl End With End If Application.ScreenUpdating = True End Sub |
Most már ha pl. az A3-as cellát kiválasztom: automatikusan hozzáadja a dinamikus tömbképletet a C3-hoz és ami a lényeg: működik a kereshető legördülő listánk!
Végezetül adjunk hozzá egy sort a forrás táblázathoz és ellenőrizzük, megjelenik-e az új tétel a legördülőben. Mivel a tömbképletek egyike a SORBA.RENDEZ, ezért az új elem az ABC sorrendnek megfelelő helyen fog megjelenni.
Fontos:
- A cellába szórész is megadható, vagyis ha beírjuk, hogy “ar” akkor a legördülő listában megjelenik pl. Armenia és Bulgaria is (ez megfelel a Contains vagyis Tartalmazza feltételnek).
- Gépeld be a szót/szórészletet -> kattints bal egérgombbal a szó mellett a cellában -> kattints a legördülő lista nyilára.
Remélem hasznos volt a mai bejegyzésem is. Ha kérdésed van a fentiek kapcsán, írj vagy hívj.
Majd’ elfelejtettem: a szóban forgó fájlok letölthetőek innen és innen 😉
