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):
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):
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.
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:
I wersja wideo:
Pozostałe artykuły o tworzeniu zależnych list rozwijanych:
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…
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ć?
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.
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 🙂
Jak przestawić separator z przecinka na inny?
Odpowiedź poniżej 🙂
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?