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

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

Dodaj komentarz

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