Czyli automatyczne kasowanie wartości po raz drugi

Ostatnio, w tym wpisie, pokazałam jak sprawić, żeby wpis na zależnej liście rozwijane sam się kasował, gdy tylko zostanie zmieniona wartość kategorii, czyli komórki, od której nasza lista rozwijana zależy. Wszystko fajnie, natomiast rozwiązanie to uwzględniało, że mamy tylko dwie komórki: z kategorią i podkategorią. Problem zaczyna się pojawiać, gdy tych komórek mamy więcej – całą listę. Taką sytuację pokazuje poniższy rysunek:

Formatka

Czyli chodzi o to, że jak zmienię wartość listy rozwijanej w kolumnie B – ma się wykasować wartość Podkategorii (kolumna C) z odpowiedniego wiersza. Znamy kolumnę komórki, która ma zostać wykasowana (C), ale nie znamy wiersza, gdyż zależy on od tego, którą komórkę wybierze użytkownik. Na tym właśnie polega cała trudność tego zadania.

Do jego rozwiązania znów posłużę się VBA, oczywiście zdarzeniem, ale tym razem będzie ono bardziej skomplikowane. Użyję do tego konstrukcji warunkowej IF (odpowiednik arkuszowej funkcji JEŻELI), właściwości Cells zakresu i jeszcze kilku innych trików 🙂

I znowu, aby rozwiązać to zadanie, należy wejść do edytora Visual Basica, czyli:

1. Otworzyć edytor makr, najlepiej skrótem klawiszowym Alt + F11.

2. Następnie na liście VBAProject kliknąć dwa razy w arkusz, w którym takie zdarzenie ma powstać. U mnie jest to arkusz Dane.

3. Następnie w oknie, które pojawi się po prawej stronie, z lewej górnej listy rozwijanej należy wybrać Worksheet, a z prawej – Change.
W ten sposób utworzymy procedurę zdarzenia arkusza, działającą na zmianę w tym arkuszu (Private Sub Worksheet_Change). Tekst, który pojawi się w okienku – zostawiamy, a w jego środek będziemy wpisywać kod makra.

Efekt i kroki postępowania pokazuje poniższy obrazek:

Tworzenie zdarzenia

A sam kod, który trzeba wpisać, tym razem jest taki:

 If Target.Column = 2 Then
      Cells(Target.Row, Target.Column + 1).ClearContents
 End If

Czyli najpierw sprawdzamy, czy komórka, w której dokonujemy zmian (Target) jest w kolumnie nr 2 naszego arkusza, czyli B. Jeśli tak – trzeba namierzyć komórkę obok i za to odpowiada instrukcja Cells. Jest to coś w stylu arkuszowej funkcji INDEKS, której tutaj podajemy wiersz i kolumnę komórki, która nas interesuje. I tak wiersz będzie taki sam, jak naszej zmienianej komórki (Target.Row), a kolumna będzie o jeden większa, czyli Target.Column + 1.

Warto na końcu wspomnieć, że to rozwiązanie, genialne w swojej prostocie, ma tę wadę, że jeśli zmienimy jakąkolwiek wartość w kolumnie B – usuniemy wartość w komórce obok. W żaden sposób nie jest bowiem sprawdzane, czy komórka w kolumnie B ma listę rozwijaną. Można to oczywiście zrobić, natomiast będzie to wymagało zdecydowanie więcej kombinowania, więc postanowiłam to pominąć. A myślę, że i tak zaprezentowane rozwiązanie wystarczy :).

I wszystko. Jak zwykle proste, jak już się wie jak zrobić :). Jeśli Ci się przyda to rozwiązanie – mam do Ciebie prośbę: pomóż mi szerzyć excelową wiedzę i podeślij swoim znajomym linka do tego wpisu. Może to rozwiązanie też im się przyda i ułatwi ich życie? Dziękuję 🙂

I wersja wideo, na której możesz na żywo zobaczyć, jak działa moje rozwiązanie.