• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Przeszukiwalna lista wyboru z podpowiedziami (formularz 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!

Chcesz się nauczyć makr w Excelu? Zacznij od nagrywania!

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:

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Dodaj do zakładek Link.

15 odpowiedzi na „Przeszukiwalna lista wyboru z podpowiedziami (formularz VBA)

  1. Konrad mówi:

    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?

  2. Konrad mówi:

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

    • Malina mówi:

      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.
      🙂

  3. Konrad mówi:

    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

  4. Konrad mówi:

    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ź.

    • Malina mówi:

      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?

  5. Piotr mówi:

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

    Dziękuję za odpowiedź

  6. robik mówi:

    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.

    • Malina mówi:

      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.

  7. Joanna mówi:

    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ź

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *