fbpx

Zależna lista rozwijana z nieposortowanych danych – zdarzenie VBA

09.08.2017 | ECP3, Makra VBA

Czyli co zrobić, aby wpisy na liście zmieniały się dynamicznie, kiedy dopisujemy dane

Przyznam, że sporo namęczyłam się, aby znaleźć rozwiązanie tego problemu. Jak już jakieś wymyśliłam, obalałam je, bo nie do końca by działało… Wymyśliłam nawet funkcję tablicową napisaną w VBA, która idealnie zwracałaby tablicę z wynikami, problem jednak polegał na tym, że lista rozwijana potrzebuje ZAKRESU, a moja wyimaginowana funkcja dawała jej tablicę. Gdyby ta tablica znajdowała się w jakimś zakresie, to ok – wszystko pięknie by działało. Ale to byłoby bez sensu: musiałabym mieć tych zakresów wiele i to nie wiadomo jakich rozmiarów… Z kolei nie mogłam napisać funkcji zwracającej zakres, ponieważ zakresu de facto nigdzie nie mam… i tak w koło Macieju.

W końcu wymyśliłam rozwiązanie, które po prostu tworzy listę rozwijaną w momencie kliknięcia w komórkę, w której lista powinna się znajdować. To natomiast jest już procedura zdarzenia VBA, więc jest ciut bardziej skomplikowane. Na szczęście żeby działało, nie trzeba tego rozumieć – wystarczy przekopiować kod 😉

O co w ogóle chodzi?

Spójrz na obrazek poniżej (formatka do naszego zadania):

Zależna lista rozwijana niesortowane dane Formatka

Formatka

Mamy tam dwie tabela: Koszty i Faktury. Chodzi o to, by do konkretnego kosztu (w tabeli z kosztami) przypisać numer faktury (z tabeli z fakturami). Numery faktur chcemy wybierać z listy i dodatkowo mają one dotyczyć tylko klienta, dla którego jest koszt. Czyli np. dla klienta Adidas chcemy wyświetlić faktury: FV/2017/J i FV/2017/AZ. Dodatkowo obie tabele się będą powiększać (są narzędziami Tabela), czyli do obu możemy dopisywać dane. I nie będziemy ich sortować za każdym razem, gdy dopiszemy, zatem nie możemy zastosować prostego rozwiązania, opisanego w tym artykule.

Tabele

Aby kod działał poprawnie, nasze tabele z danymi muszą być narzędziami Tabela (możesz taką stworzyć za pomocą skrótu Ctrl + t, stojąc w obrębie zakresu danych). Muszą się też nazywać odpowiednio: tbFaktury i tbKoszty. Jak już to mamy, możemy przejść do omówienia logiki makra, bo tak – do rozwiązania tego problemu potrzebne jest makro 🙂

Logika makra

Najpierw będziemy ustalać dla jakiego klienta mają zostać wyświetlane faktury, np. Adidas. Klient ten będzie dokładnie w tym samym wierszu co zaznaczona komórka (Target), w kolumnie drugiej arkusza, czyli B. Następnie sprawdzimy w tabeli tbFaktury gdzie występuje ten klient. Przejedziemy pętlą (For) kolumnę Klient tejże tabeli i wychwycimy wiersze, w których się znajduje. W naszej formatce są to wiersze 2. i 4. Dokładnie w tych samych wierszach, w kolumnie Nr FV są numery faktur, które chcemy wyświetlić. Posłuży nam do tego zwykła zmienna tekstowa (String), która zapisze wszystkie te faktury. Tych zmiennych będzie znacznie więcej. Zanim jednak przejdziemy do przekładania tej logiki na kod – stwórzmy najpierw procedurę zdarzenia, która umożliwi nam tworzenie listy rozwijanej.

Tworzenie zdarzenia arkusza

Najpierw wejdź do edytora VBA za pomocą skrótu Alt + F11. Teraz w okienku Project explorer po lewej stronie ekranu kliknij dwukrotnie w pozycję Dane (jest to nazwa arkusza, do którego chcesz przypisać zdarzenie):

Tworzenie zdarzenia arkusza

Tworzenie zdarzenia arkusza

Następnie w okienku, które się pojawi po prawej stronie – kliknij w prawą listę rozwijaną i wybierz SelectionChange. Jest to nazwa zdarzenia, podczas którego ma się stworzyć nasza lista rozwijana.

Tworzenie zdarzenia arkusza SelectionChange

Tworzenie zdarzenia arkusza: SelectionChange

Pojawi się następnie następujący wpis w okienku kodu i będzie to już nasza (pusta) procedura zdarzenia, której nazwy nie tykamy!:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Całą treść procedury wpiszemy w środek. A ta treść to…

Kod procedury

Najpierw pokażę Ci cały kod, a potem po kolei go omówię. Cały kody wygląda następująco:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim Wiersz As Long, Kolumna As Long, MaxWiersz As Long
   Dim Klient As String, Klienci As Range, NumeryFV As Range
   Dim Licznik As Long, TrescListy As String
 
   Wiersz = Target.Row
   Kolumna = Target.Column
   MaxWiersz = Range("tbKoszty").Rows.Count + 5
 
 If (Kolumna = 5) And (Wiersz > 5 And Wiersz <= MaxWiersz) Then
    Klient = Cells(Wiersz, 2).Value
    Set Klienci = Range("tbFaktury").Columns(2)
    Set NumeryFV = Range("tbFaktury").Columns(1)
    MaxWiersz = Klienci.Rows.Count
 
    For Licznik = 1 To MaxWiersz
       If Klienci.Cells(Licznik, 1).Value = Klient Then TrescListy = TrescListy & "," & _
       NumeryFV.Cells(Licznik, 1)
    Next
    If TrescListy = "" Then TrescListy = "Brak faktur"
 
    With Target.Validation
       .Delete
       .Add Type:=xlValidateList, Formula1:=TrescListy
    End With
 End If
End Sub

Na samym początku, zanim jeszcze zacznie się kod, który coś robi, najpierw deklarujemy zmienne. Odpowiada za to ten fragment kodu:

 Dim Wiersz As Long, Kolumna As Long, MaxWiersz As Long
 Dim Klient As String, Klienci As Range, NumeryFV As Range
 Dim Licznik As Long, TrescListy As String

Następnie, zgodnie z logiką, którą opisywałam wcześniej, sprawdzamy, czy zaznaczona komórka jest w tabeli tbKoszty w kolumnie Koszt do FV. Robi to ten fragment:

   Wiersz = Target.Row
   Kolumna = Target.Column
   MaxWiersz = Range("tbKoszty").Rows.Count + 5
 
 If (Kolumna = 5) And (Wiersz > 5 And Wiersz <= MaxWiersz) Then
   
 End If

Zauważ, że ręcznie wpisałam liczbę 5 w kilku miejscach. Jest to liczba wierszy w arkuszu nad tabelami. Jeśli planujesz umieścić swoje tabele w innym miejscu – odpowiednio zmień tę wartość. Warto w ogóle stworzyć osobną zmienną w tym celu, jeśli jeszcze nie wiesz dokładnie, gdzie chcesz te tabele umieścić – wartość zmiennej zmieniasz w jednym miejscu, a wykorzystać ją możesz w kilku. To tak, jak blokowanie komórek znakami $ 😉

Później następuje przypisanie odczytanie klienta, dla którego chcemy wylistować faktury i przypisanie do zmiennych zakresów w tabeli tbFaktury, które będą przeszukiwane:

    Klient = Cells(Wiersz, 2).Value
    Set Klienci = Range("tbFaktury").Columns(2)
    Set NumeryFV = Range("tbFaktury").Columns(1)
    MaxWiersz = Klienci.Rows.Count

I tutaj znów ręcznie wpisałam numery kolumn w tabelach: 2, 2 i 1. Jeśli u Ciebie są w innym miejscu – zmień je tutaj.

Później to już tylko przejechanie pętlą For po zakresie z klientami i, jeśli klient się zgadza, wczytanie odpowiadającej mu komórki do zmiennej TrescListy:

    For Licznik = 1 To MaxWiersz
       If Klienci.Cells(Licznik, 1).Value = Klient Then TrescListy = TrescListy & "," & _
       NumeryFV.Cells(Licznik, 1)
    Next
    If TrescListy = "" Then TrescListy = "Brak faktur"

Ostatni fragment tworzy listę rozwijaną w komórce (Target) z taką treścią, jak zawartość zmiennej TreśćListy. Zauważ, że na początku dotychczasowa lista w komórce jest kasowana (.Delete). Jest to o tyle wygodne, że wielokrotnie możemy korzystać z listy dla danego klienta, a wartości na niej będą zawsze  aktualne:

    With Target.Validation
       .Delete
       .Add Type:=xlValidateList, Formula1:=TrescListy
    End With

Wszystko pięknie hula, zobaczcie na obrazku, a najlepiej na filmie:

Zależna lista rozwijana niesortowane dane Wynik

Wynik

I wersja wideo:

 

Plik do pobrania jest tutaj (pamiętaj, aby włączyć makra!):

Pozostałe artykuły o tworzeniu zależnych list rozwijanych:

 

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

7 komentarzy

  1. Działa! Dziękuję! 😀

    Musiałam trochę przemodyfikować kod, żeby działało w moich warunkach, ale jest PRAWIE idealnie 🙂

    PRAWIE – bo co zrobić, jeśli (przekładając na Twój tutorial) w numerze faktury wystąpi znak przecinek? Symbol przecinka jest równocześnie separatorem, zatem jak trafi na ten znak, robi z tego kolejną pozycję. Nie interesuje go, że przecinek to część numeru fv i ma tego NIE DZIELIĆ… Jak to obejść? Bo o ile widzę, że tu ustawiasz separator – przecinek:

    If Klienci.Cells(Licznik, 1).Value = Klient Then TrescListy = TrescListy & “,” & NumeryFV.Cells(Licznik, 1)

    To nie wiem jak tu zmienić znak dekompresji:

    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=TrescListy
    End With

    Jak sobie ustawiłam w tym miejscu inny separator:

    f Klienci.Cells(Licznik, 1).Value = Klient Then TrescListy = TrescListy & “^” & NumeryFV.Cells(Licznik, 1)

    To wszystko się posypało…

    Reply
    • Bardzo się cieszę, że działa!!! :).
      Odnośnie separatora: przecinek jest separatorem listy w VBA. VBA jest Amerykaninem, stąd przecinek (w polskiej wersji jest to średnik, ale nie ma to znaczenia dla VBA). Dlatego się posypało z innym separatorem… Spróbuj jednak przecinek :). A jeśli chodzi o numer FV… hmmm.. tak, to fakt – będzie problem ;(. Może da się tam zamienić?

      Reply
      • U mnie na liście rozwijanej są nazwy potraw. Trochę niewygodne dla mnie jest wprowadzać ograniczenie zakazu używania przecinka w nazwie, bo jak np miałabym zamienić “Kurczak z jabłkami, kaszą i surówką”? 🙁 Szukałam, czy można zdefiniować inny znak separatora, ale nic nie znalazłam… a w tym momencie na liście rozwijanej mam:

        Kurczak z jabłkami
        kaszą i surówką

        jako dwie pozycje.

        Reply
        • Ok, rozumiem… to faktycznie słabo ;(. Jest jeszcze jedna opcja – zdecydowanie bardziej “przyszłościowa”, mianowicie w arkuszu roboczym tworzyć w zakresie listę wpisów, i w sprawdzaniu poprawności odwołać się do tej listy. Czyli zamiast tego fragmentu:

          If Klienci.Cells(Licznik, 1).Value = Klient Then TrescListy = TrescListy & “,” & _

          Trzeba byłoby dać dopisywanie wartości do zakresu. I ten zakres potem czyścić dla kolejnych danych. Jeszcze większa kombinacja, ale byłybyśmy niezależne od separatora listy, ponieważ źródłem do listy byłby zakres 🙂

          Reply
  2. Jak przestawić separator z przecinka na inny?

    Reply
    • Odpowiedź poniżej 🙂

      Reply
  3. A co w przypadku, jeżeli zamiast wyrażenia “brak faktur”, czyli w przypadku kiedy Lista Rozwijana jest pusta, chciałabym zrobić by było możliwe wpisane dowolnej wartości do komórki?

    Reply

Submit a Comment

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