Czyli zależne listy rozwijane – kolejna odsłona

To już któryś z kolei wpis o zależnych listach rozwijanych. Dziś jednak będzie trochę inaczej, ponieważ samych list nie będę tykać, tylko pokażę jak skasować wpis na zależnej liście rozwijanej, po zmianie wyboru z pierwszej listy (tej, od której jest zależna).

Formatka jest prosta: potrzebujemy dwóch list rozwijanych (żółte pola): pole kategorii i pole podkategorii:

Formatka

Po wyborze kategorii, do pola podkategorii wczytuje się odpowiednia lista rozwijana, w zależności od wyboru kategorii (o tym, jak to zrobić pisałam np. tutaj). Problem jednak polega na tym, że jak wybierzemy jakąś kategorię i podkategorię, a potem zmienimy kategorię – wybrana wcześniej podkategoria zostanie i, oczywiście, nie będzie pasowała do nowej kategorii. Można ją oczywiście wykasować, ale mi chodzi o to, aby trochę użytkownika wyręczyć i zrobić, aby Excel sam kasował wartość podkategorii, gdy zmieni się kategoria. Natomiast to już jest pole do popisu dla VBA i o tym będzie dzisiejszy wpis.

Zanim jednak przejdziemy do VBA – nazwijmy żółte komórki. Lubię to robić, zwłaszcza w VBA, ponieważ jak powołuję się na nazwę komórki, jestem wtedy niezależna od jej adresu.

Nazywanie komórek

Najprostszą metodą na nazwanie komórki jest zaznaczenie jej i wpisanie nazwy do pola nazwy (pole po lewej stronie od paska formuły). Na obrazku zaznaczyłam komórkę C3 (kategoria) i w polu nazwy nadałam jej nazwę Kategoria. Nadanie nazwy trzeba zatwierdzić Enterem:

Nadawanie nazwy komórce

To samo trzeba zrobić dla komórki C5 – ja jej nadałam nazwę Podkategoria.

Jak już to mamy zrobione – można przejść do pisania makra, a konkretnie zdarzenia arkusza.

Treść makra

Właśnie, nasze makro będzie zdarzeniem, czyli powiemy Excelowi, żeby za każdym razem, jak zmieni się konkretna komórka (Kategoria) – kasował wartość innej komórki (Podkategorii). Ma to robić „sam z siebie”, czyli nie będziemy naciskać żadnego przycisku, żeby takie coś się stało. To jest właśnie zdarzenie.

Żeby takie zdarzenie stworzyć, należy:

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 procedury zdarzenia

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

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("Kategoria").Address Then
        Range("Podkategoria").ClearContents
    End If

End Sub

Jak to działa?

Makro sprawdza, czy adres zmienianej komórki (Target) jest adresem komórki, którą nazwaliśmy Kategoria. Jeśli jest – makro kasuje zawartość komórki Podkategoria. I cała filozofia 🙂

Efekt jest taki, że jak zmienimy kategorię – od razu zostanie skasowana podkategoria, czyli dokładnie to, o co chodziło :).

W tym rozwiązaniu pokazałam metodę działającą na jednej komórce. Co jednak, gdy takich komórek, które chcemy kasować mamy więcej? Np. w tabeli? O tym piszę tutaj.

Oto wersja wideo tego rozwiązania:

I plik z gotowym kodem do pobrania:

Powiązane wpisy:

• Zależna lista rozwijana w komórce
• Dwie zależne listy rozwijane
• Zależna lista rozwijana z nieposortowanych danych