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):

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

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

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:

Wynik

I wersja wideo:

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

• Zależna lista rozwijana w komórce
• Dwie zależne listy rozwijane i formuła tablicowa