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:
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
A oto założenia:
- Formularz wywoływany na zdarzenie podwójnego kliknięcia w nazwanym zakresie (kolumna Klient w tabeli tbFaktury)
- Kolumna Klient w tabeli tbFaktury jest nazwana ZakresKlientow
- Kolumna Klienci w tabeli tbKlienci jest nazwana Klienci
A kolejne kroki użytkownika są takie:
- Użytkownik dwuklika w komórkę, do której chce wstawić klienta
- Pojawia się formularz
- Wpisuje szukaną frazę
- Wybiera z listy klienta, zatwierdza
- 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
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):
- Label, czyli etykieta, czyli nasz tekst Wpisz szukany tekst.
- TextBox, czyli pole tekstowe, do którego użytkownik wpisuje szukany fragment nazwy klienta (nazwałam go w kodzie txtSzykanyTekst)
- CommandButton, czyli przycisk Szukaj, który użytkownik będzie naciskał, gdy wpisze szukaną frazę (nazwa: btnSzukaj)
- ListBox, czyli lista, na której wyświetlą się znalezieni klienci, a użytkownik będzie mógł na nim wybrać klienta (nazwa: lbxPropozycje)
- 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
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
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
Następnie wpisujemy kod (widoczny na obrazku powyżej), aktywujący kontrolkę btnSzukaj, czyli:
Private Sub txtSzykanyTekst_Change()
btnSzukaj.Enabled = True
End SubAktywacja 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:
- Czyszczenie listy (na wszelki wypadek, gdyby user szukał różnych tekstów),
- Zmiana wielkości liter szukanego tekstu (VBA jest case sensitive!)
- 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 SubOprogramowanie przycisku WSTAW
Ok. Zakładamy, że user wybrał klienta, naciska przycisk Wstaw i co teraz? Teraz powinniśmy:
- wstawić wybranego klienta do aktywnej komórki arkusza,
- zamknąć formularz.
Czyli tak:
Private Sub btnWstaw_Click()
Dim WybranyTekst As String
WybranyTekst = lbxPropozycje.Value
ActiveCell.Value = WybranyTekst
Unload frmSzukaj
End SubI 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 SubI 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:
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?
W sumie to sam to rozwiązałem.
Wystarczy wszystko co jest w „btnSzukaj_Click()” przenieść do „txtSzykanyTekst_Change()”.
Pozdrawiam
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.
🙂
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
Ooo! To super pomysł! Dzięki wielkie! 🙂
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ź.
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?
Witam, jak zrobić, aby dodatkowo w ibxPropozycje od razu pojawiała się lista wszystkich danych z tbKlienci ??
Dziękuję za odpowiedź
Hej, w tej kontrolce należy ustawić właściwość „Rowsource” na tbKlienci 🙂
Super, dzięki za pomoc !!
Cieszę się bardzo, że pomogłam 🙂
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ź 😉
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.
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.
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ź
Hej, czyli rozumiem, że chcesz zależną listę?
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.
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.
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”)”
Hej, a czy masz tę nazwę „ZakresKlientow” zdefiniowaną w swoim arkuszu? Koniecznie musi być 🙂
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 🙂
Ola, hej,
to super pomysł. Zrobiłabym to korzystając z filtru zaawansownego. A podasz przykład kryteriów?
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ę
Hej 🙂
A powiedz, co konkretnie chciałabyś zrobić? Będzie mi łatwiej 🙂
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)
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?
Jeszcze jedno pytanko 😉
jak włączyć w formularzu scroll tak aby działał przy przewijaniu kółkiem myszki?
Witam,
A w którym miejscu podać ścieżkę do drugiego, oddzielnego arkusza excel z taką listą klientów?