fbpx

Przeszukiwalna lista wyboru z podpowiedziami (formularz VBA)

26.04.2020 | ECP3, Makra VBA

Czyli sposób na łatwe szukanie klienta z VBA

Temat, o który już dawno mnie pytaliście, ale przyznaję szczerze: nie chciało mi się za niego zabierać :). W końcu jednak nadszedł czas! Obecnie, z nowym mechanizmem przeliczania w Excelu 365, zrobienie czegoś takiego za pomocą formuł nie jest już wielkim wyzwaniem. Natomiast wyzwaniem jest to, że nadal nie wszyscy mają Excela w tej wersji. I szybko to się nie zmieni… Dlatego chciałabym, aby rozwiązanie było możliwe w każdej wersji Excela.

Ok, ale o co w ogóle chodzi?

O przeszukiwalną listę. Chciałabym napisać rozwijaną, ale to za bardzo ograniczyłoby mi możliwości rozwiązania, choć na pewno byłoby najbardziej użyteczne.

Chodzi o zrobienie takiej listy wyboru np. klientów, aby po wpisaniu kilku liter (niekoniecznie początkowych), Excel wylistował klientów, zawierających wpisane litery. Z nich użytkownik wybiera klienta, który ma być wpisany do komórki.

Chodzi o coś takiego:

VBA Przeszukiwalna lista klientów

 

Czyli po dwukrotnym kliknięciu w kolumnie z klientami pojawia się formularz, na którym możemy wpisać szukaną frazę. Nasz wybór zostanie wpisany do aktywnej komórki arkusza.

Zadanie do najłatwiejszych nie należy, rozwiązanie będzie złożone i długie (dlatego tak długo zwlekałam ;)). Ale damy radę! Do dzieła!

Omówienie formatki i założenia

Formatka to prosta tabela o nazwie tbFaktury w arkuszu Dane, oraz jednokolumnowa tabela tbKlienciw arkuszu Klienci:

Przeszukiwalna lista formularz VBA-Formatka

Przeszukiwalna lista formularz VBA-Formatka

A oto założenia:

  1. Formularz wywoływany na zdarzenie podwójnego kliknięcia w nazwanym zakresie (kolumna Klient w tabeli tbFaktury)
  2. Kolumna Klient w tabeli tbFaktury jest nazwana ZakresKlientow
  3. Kolumna Klienci w tabeli tbKlienci jest nazwana Klienci

A kolejne kroki użytkownika są takie:

  1. Użytkownik dwuklika w komórkę, do której chce wstawić klienta
  2. Pojawia się formularz
  3. Wpisuje szukaną frazę
  4. Wybiera z listy klienta, zatwierdza
  5. Klient zostaje wstawiony do aktywnej komórki

Zacznijmy więc od stworzenia formularza.

Tworzenie formularza przeszukiwalnej listy

1. W tym celu, w oknie VBA (Alt + F11), w naszym projekcie, tworzymy nowy formularz: Insert/UserForm.

2. Nazywamy go frmSzukaj, w okienku Properties:

Nazywanie formularza

Nazywanie formularza

3. A teraz prace plastyczno-techniczne, czyli umieszczanie kontrolek na formularzu:

Potrzebujemy następujące kontrolki (nazwy zmieniamy tak, jak w przypadku formularza – w okienku Properties):

  1. Label, czyli etykieta, czyli nasz tekst Wpisz szukany tekst.
  2. TextBox, czyli pole tekstowe, do którego użytkownik wpisuje szukany fragment nazwy klienta (nazwałam go w kodzie txtSzykanyTekst)
  3. CommandButton, czyli przycisk Szukaj, który użytkownik będzie naciskał, gdy wpisze szukaną frazę (nazwa: btnSzukaj)
  4. ListBox, czyli lista, na której wyświetlą się znalezieni klienci, a użytkownik będzie mógł na nim wybrać klienta (nazwa: lbxPropozycje)
  5. CommandButton, czyli przycisk, który umożliwi wstawienie wybranego klienta do arkusza i zamknięcie formularza (nazwa: btnWstaw).

Poniższy obrazek pokazuje te elementy:

Kontrolki formularza

Kontrolki formularza

Trochę to zagmatwane, ale da się zrobić :).

W przypadku obu przycisków – btnSzukaj i btnWstaw, ustawiamy je jako nieaktywne. Staną się aktywne dopiero, gdy użytkownik:

  • btnSzukaj – wpisze cokolwiek do pola tekstowego
  • btnWstaw – wybierze klienta z listy.

Aby ustawić tę właściwość, użyj okienka Properties i ustaw Enabled na false:

Przyciski na początku mają być nieaktywne

Przyciski na początku mają być nieaktywne

Aktywacja przycisku SZUKAJ – zdarzenie pola tekstowego

To teraz napiszmy zdarzenie, które spowoduje aktywację tych przycisków. Dla przycisku SZUKAJ, czyli od btnSzukaj, będzie to zdarzenie Change pola tekstowego txtSzykanyTekst. Zdarzenia elementów formularza tworzymy tak samo, jak zdarzenia np. arkusza. Dwukrotnie klikamy na element i z list nad okienkiem kodu wybieramy odpowiednie zdarzenie.

Aktywacja przycisku SZUKAJ

Aktywacja przycisku SZUKAJ

Następnie wpisujemy kod (widoczny na obrazku powyżej), aktywujący kontrolkę btnSzukaj, czyli:

Private Sub txtSzykanyTekst_Change()
     btnSzukaj.Enabled = True
End Sub

Aktywacja przycisku WSTAW – zdarzenie listy

To samo robimy dla listy lbxPropozycje, zdarzenie Change, czyli:

Private Sub lbxPropozycje_Change()
   btnWstaw.Enabled = True
End Sub

Oprogramowanie przycisku SZUKAJ

Teraz pora na określenie co ma się stać. gdy user naciśnie przycisk Szukaj, czyli btnSzukaj.

Ma się stać tak:

  1. Czyszczenie listy (na wszelki wypadek, gdyby user szukał różnych tekstów),
  2. Zmiana wielkości liter szukanego tekstu (VBA jest case sensitive!)
  3. Przeszukanie listy klientów w poszukiwaniu odpowiednich – jak pasuje → wpisz na listę

Powyższe kroki wykonuje ten kod:

Private Sub btnSzukaj_Click()
    Dim Klienci As Range, Komorka As Range
    Dim SzukanyTekst As String, ZnalezionyTekst As String
    
    lbxPropozycje.Clear
    
    SzukanyTekst = LCase(txtSzykanyTekst.Value)
    
    Set Klienci = Sheets("Klienci").Range("Klienci")
        Klienci.Sort Klienci.Cells(1, 1), xlAscending, Header:=xlYes
        
        For Each Komorka In Klienci
            ZnalezionyTekst = LCase(Komorka.Value)
            If InStr(1, ZnalezionyTekst, SzukanyTekst) > 0 Then
                lbxPropozycje.AddItem Komorka.Value
            End If
        Next
End Sub

Oprogramowanie przycisku WSTAW

Ok. Zakładamy, że user wybrał klienta, naciska przycisk Wstaw i co teraz? Teraz powinniśmy:

  1. wstawić wybranego klienta do aktywnej komórki arkusza,
  2. zamknąć formularz.

Czyli tak:

Private Sub btnWstaw_Click()
    Dim WybranyTekst As String
    
    WybranyTekst = lbxPropozycje.Value
    
    ActiveCell.Value = WybranyTekst
    Unload frmSzukaj
      
End Sub

I to by było na tyle, jeśli chodzi o to, co się dzieje na formularzu. Teraz tylko pozostaje pytanie: OK, ale jak wywołać formularz?

Wywołanie formularza – zdarzenie arkusza

Wywołamy go poprzez dwukrotne kliknięcie w kolumnie z klientami w tabeli. Przypomnę, że ten zakres nazwałam ZakresKlientow.

W tym celu należy utworzyć zdarzenie BeforeDoubleClick w arkuszu, gdzie znajduje się nasza tabela. U mnie to jest arkusz Dane.

A kod tego zdarzenia to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ZakresKlientow As Range
    
    Set ZakresKlientow = Me.Range("ZakresKlientow")
    
    If Not Intersect(Target, ZakresKlientow) Is Nothing Then
        Cancel = True
        frmSzukaj.Show
    End If
    
End Sub

I to jest wszystko! Trochę kodu to rozwiązanie wymaga i do najprostszych nie należy.

Powiązane produkty

  • Nagrywanie makr – podczas tej dwugodzinnej lekcji dowiesz się jak w sprytny sposób nagrywać całkiem inteligentne makra
  • O obiekcie tabela słów kilka – podczas tej lekcji pokazuję czym jest obiekt tabela w Excelu i dlaczego warto z niego korzystać. Daje nam on ogromne możliwości dynamizowania naszych arkuszy, a wiele nowych funkcjonalności Excela wręcz wymaga przechowywanie danych w tymże obiekcie (np. Power Query). Must have każdego użytkownika Excela!
  • Kurs Excel w codziennej pracy – mający na celu uporządkować Twoją wiedzę na temat Excela. Jest świetny zarówno dla osób, które chcą uporządkować swoją wiedzę, zdobywaną do tej pory „po omacku”, jak i takich, które dopiero zaczynają swoją przygodę z Excelem.

 

Plik do pobrania (zip):

MalinowyExcel VBA Lista z podpowiedziami dw.zip

 

 

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie:

 

 

Jeżeli chcesz lepiej poznać makra i VBA zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 3! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

28 komentarzy

  1. Bardzo ciekawy wpis, dziękuję.
    Czy można zrobić tak, aby dane wyszukiwały się dynamicznie (podczas wpisywanych liter w miejscu txtSzukanyTekst) bez użycia przycisku Szukaj?

    Reply
  2. W sumie to sam to rozwiązałem.
    Wystarczy wszystko co jest w „btnSzukaj_Click()” przenieść do „txtSzykanyTekst_Change()”.
    Pozdrawiam

    Reply
    • Cieszę się, że wpis Ci się podoba :).
      Super, że sobie poradziłeś! Tylko trzeba uważać na szybkość działania makra przy duuuużej liczbie klientów.
      🙂

      Reply
  3. Dodałem jeszcze dwuklik na wyszukanej pozycji, który wprowadza dane zamiast przycisku „btnWstaw”.
    Całość wygląda tak:

    Private Sub txtSzykanyTekst_Change()

    Dim Klienci As Range, Komorka As Range
    Dim SzukanyTekst As String, ZnalezionyTekst As String

    lbxPropozycje.Clear

    SzukanyTekst = LCase(txtSzykanyTekst.Value)

    Set Klienci = Sheets(„Klienci”).Range(„Klienci”)
    Klienci.Sort Klienci.Cells(1, 1), xlAscending, Header:=xlYes

    For Each Komorka In Klienci
    ZnalezionyTekst = LCase(Komorka.Value)
    If InStr(1, ZnalezionyTekst, SzukanyTekst) > 0 Then
    lbxPropozycje.AddItem Komorka.Value
    End If
    Next

    End Sub

    Private Sub lbxPropozycje_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim WybranyTekst As String
    WybranyTekst = lbxPropozycje.Value
    ActiveCell.Value = WybranyTekst

    Unload frmSzukaj
    End Sub

    Reply
    • Ooo! To super pomysł! Dzięki wielkie! 🙂

      Reply
  4. Mam jeszcze 2 pytania:
    1. Jak zrobić, aby po dwukliku (lub naciśnięciu przycisku „Wstaw”) nie wstawiało danych klienta, tylko przenosiło nas do bazy danych z klientami i zaznaczało tego, którego wyszukaliśmy?
    2. Czy można wyszukiwanie rozszerzyć do dwóch kolumn (np. dodatkowo o NIP klienta)? Czyli żeby można było w jednym miejscu „txtSzukanyTekst” wpisywać nazwę lub NIP, a Excel w polu „lbxPropozycje” poda nam wyniki.

    Z góry dziękuję za odpowiedź.

    Reply
    • Hej,
      1. ja bym tutaj w arkuszu z klientami zrobiła szukanie (metoda Find) – można nawet nagrać makro → Ctrl + F.
      2. To tutaj wolałabym jednak rozgraniczyć: dać 2 pola tekstowe – jedno NIP, drugie nazwa; lub zrobić rozgraniczenie polem opcji obok (user zaznacza, co wpisuje). Nie szła bym w jedno dwufunkcyjne pole.
      Może tak?

      Reply
  5. Witam, jak zrobić, aby dodatkowo w ibxPropozycje od razu pojawiała się lista wszystkich danych z tbKlienci ??

    Dziękuję za odpowiedź

    Reply
    • Hej, w tej kontrolce należy ustawić właściwość „Rowsource” na tbKlienci 🙂

      Reply
      • Super, dzięki za pomoc !!

        Reply
        • Cieszę się bardzo, że pomogłam 🙂

          Reply
          • Witam
            Trochę odgrzeję temat;) gdy ustawiłem kontrolkę „Rowsource” zgodnie z Twoją informacją to w momencie gdy wciskam przycisk szukaj pojawia mi się błąd: run-time error 70 permission danied.
            Jak można go obejść? Będę wdzięczny za szybką odpowiedź 😉

  6. Prezentowane rozwiązanie jest nieskalowalne i bardzo ograniczone. Pracowanie na dużych zakresach przy użyciu podanego rozwiązania to masakra! Proponujesz rozwiązania niesprawdzone i nieefektywne.
    Zwłaszcza zmodyfikowane makro Konrada: przy każdym wciśnięciu klawisza wywoływane jest sortowanie tabeli i pętla przechodząca po każdej komórce!
    Do tego każdorazowe czyszczenie formularza.

    Moja propozycja, najpierw kod, potem omówienie.
    Pewnie jest jeszcze miejsce na optymalizację ale mimo to działa X razy szybciej.

    ###

    Option Explicit

    Private m_avKlienci As Variant

    Private Sub UserForm_Initialize()
    m_avKlienci = Sheets(„Klienci”).ListObjects(„tbKlienci”).DataBodyRange.Value
    End Sub

    Private Sub UserForm_Activate()
    txtSzykanyTekst.Value = vbNullString
    txtSzykanyTekst.SetFocus
    lbxPropozycje.list = m_avKlienci
    lbxPropozycje.ListIndex = -1
    lbxPropozycje.TopIndex = 0
    End Sub

    Private Sub txtSzykanyTekst_Change()
    lbxPropozycje.list = Szukaj(txtSzykanyTekst.Value, chkUwzglWlkZnakow)
    End Sub

    Private Sub lbxPropozycje_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveCell.Value = lbxPropozycje.list(lbxPropozycje.ListIndex)
    Me.Hide
    End Sub

    Private Function Szukaj(SzukanyTekst As String, chkWielkoscZnakow As Boolean) As Variant()

    Dim list As Object
    Dim i As Long
    Dim ikWielkoscZnakow As Integer
    Set list = CreateObject(„System.Collections.ArrayList”)

    ikWielkoscZnakow = IIf(chkWielkoscZnakow, 0, 1)

    For i = LBound(m_avKlienci) + 1 To UBound(m_avKlienci)
    If InStr(1, m_avKlienci(i, 1), SzukanyTekst, ikWielkoscZnakow) > 0 Then
    list.Add m_avKlienci(i, 1)
    End If
    Next

    list.Sort
    Szukaj = list.ToArray
    End Function

    Private Sub chkUwzglWlkZnakow_Click()
    lbxPropozycje.list = Szukaj(txtSzykanyTekst.Value, chkUwzglWlkZnakow)
    End Sub

    Private Sub cmbClose_Click()
    Me.Hide
    End Sub

    ###

    Po pierwsze, inicjalizacja formularza UserForm_Initialize czyli utworzenie obiektu, załadowanie tabeli do array – zmienna modułowa m_avKlienci
    Wywoływane tylko raz!

    Drugi kod to aktywacja UserForm_Activate – formularz już jest w pamięci, jedynie czyścimy wyszukiwany ciąg znaków i wybór z listy, przywracamy całą listę klientów – wszystko z pamięci.

    Trzeci kod txtSzykanyTekst_Change – funkcja Szukaj wywoływana przy każdym naciśnięciu klawisza która zwraca odfiltrowaną listę (opiszę poniżej)

    Czwarty kod lbxPropozycje_DblClick – wpisanie do komórki aktualnego wyboru

    Funkcja Szukaj – przyjmuje dwa argumenty: szukany ciąg znaków oraz chkWielkoscZnakow
    Jest to moja propozycja, dodatkowy CheckBox na formularzu – uwzględnianie wielkości znaków w wyszukiwaniu
    Utworzenie kolekcji ArrayList – ma tę przewagę nad zwykłym array że ma bardzo szybką wbudowaną metodę sortowania oraz może dynamicznie zmieniać rozmiar (nie ma potrzeby ReDim Preserve)
    Wyszukiwanie odbywa się na array m_avKlienci – w pamięci, więc jest bardzo szybkie.
    Output z funkcji przepisany do array i załadowany do lbxPropozycje

    Ponadto zamiana Unload formularza na Hide. Proszę poczytać o różnicy.

    Dla dodatkowej wygody użytkownika można dodać przycisk do zamykania formularza klawiszem ESC – Command Button o rozmiarze 0 z właściwością Cancel = True
    Klawisz szukaj w tej sytuacji zbędny, podobnie Wstaw.

    Przy 100000 rekordów u mnie filtrowanie prawie w czasie rzeczywistym.

    Reply
    • Dziękuję za propozycję rozwiązania i obszerne wyjaśnienia. Na pewno przyda się wielu osobom, które mają większe bazy danych.
      Mam też do Ciebie prośbę, abyś komentując na moim blogu unikał oceniania. Bardzo tego nie lubię. Wolę dyskusję, nie oceny.

      Reply
  7. Super artykuł, długo takiego rozwiązania szukałam tylko nie bardzo mogę go użyć do siebie bo mam zależną listę rozwijaną.

    Czy można by było to jakoś zmodyfikować? Czyli załóżmy bazując na Twoim przykładzie, do każdego miasta mam klientów?
    Tak by po określeniu miasta, w formularzu pojawiały się nie wszyscy klienci z kraju a tylko z obszaru danego miasta.
    Dziękuję za odpowiedź

    Reply
    • Hej, czyli rozumiem, że chcesz zależną listę?

      Reply
  8. Dzień dobry,
    Bardzo ciekawy wpis. Czy możliwe jest zaprogramowanie formularza tak, aby w jednej komórce możliwe było dokonanie wielokrotnego wyboru – czyli np. po pierwszym wyborze Jagi Zielonki, można było dodać kolejną osobę? Na tej samej zasadzie co w Twoim tutorialu dotyczącym list rozwijanych.

    Reply
    • Hmmm… można byłoby na formularzu zaznaczyć kilka opcji, a potem wpisać je (pętlą najpewniej) do wybranej komórki. Pętlą, ponieważ dostajemy od formularza kilka wartości.

      Reply
  9. Hej, ja stworzyłem u siebie w arkuszu, tą listę z podpowiedziami, ale wywala mi błąd
    Run-time error 1004
    Method Range of object _Worksheet Failed
    Klikam debug
    I podświetla się linijka „Set ZakresKlientow = Me.Range(„ZakresKlientow”)”

    Reply
    • Hej, a czy masz tę nazwę „ZakresKlientow” zdefiniowaną w swoim arkuszu? Koniecznie musi być 🙂

      Reply
  10. Cześć,
    Rewelacyjny formularz!
    Mam pytanie jak zaprogramować dodatkowe 2 pola tekstowe (filtry) tak aby wyświetlana lista została ograniczona do wartości, spełniających wszystkie kryteria.
    Zakładam, że poza polem tekstowym txtSzukanyTekst, pole1 przeszukuje zakres1 np. miasto, a pole2 przeszukuje zakres2 np. ulicę.
    Z góry dziękuję za pomoc 🙂

    Reply
    • Ola, hej,
      to super pomysł. Zrobiłabym to korzystając z filtru zaawansownego. A podasz przykład kryteriów?

      Reply
  11. Witam,
    Super tutorial! W mojej pracy potrzebuję ograniczenia wyszukiwania dodatkowymi filtrami. Czy planujesz dalszy ciąg tego tutorial z podobnym rozszerzeniem? Poszę o podpowiedź jak ugryźć ten temat z wykorzystaniem prezentowanego kodu.
    Dziękuję

    Reply
    • Hej 🙂
      A powiedz, co konkretnie chciałabyś zrobić? Będzie mi łatwiej 🙂

      Reply
  12. Ukłony za tą jak i inne Twoje treści. Mam jedynie problem aby dwa różne formularze pojawiły się w arkuszu. Wywraca się ostatnie makro z podwójnym klikiem. Masz na to jakąś radę? (komunikat ambigous name detected)

    Reply
    • Bardzo się cieszę, że moje porady CI się przydają 🙂
      Ten komunikat mówi o tym, że w tym wypadku dwa formularze tak samo się nazywają. Podmień proszę nazwę jednego i komunikat nie powinien już się pokazywać :). Pozostaje jeszcze tylko kwestia który i kiedy powinien być wywołany?

      Reply
  13. Jeszcze jedno pytanko 😉
    jak włączyć w formularzu scroll tak aby działał przy przewijaniu kółkiem myszki?

    Reply
  14. Witam,
    A w którym miejscu podać ścieżkę do drugiego, oddzielnego arkusza excel z taką listą klientów?

    Reply

Leave a Reply to Malina Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest