fbpx

Zależna lista rozwijana – automatyczne usuwanie podkategorii po zmianie kategorii (VBA)

10.11.2017 | ECP3, Makra VBA

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

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

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

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:

 

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

4 komentarze

  1. Jeśli mam kilka takich list rozwijanych powiązanych ze sobą i chce żeby w każdej dział kod automatycznego usuwania to jak to zapisać?

    Reply
    • Ja bym tutaj skorzystała z instrukcji Intersect, jeśli miałoby być więcej komórek, które miałyby podlegać takim zmianom. Czyli if not Intersect (Zakres1, Zakres2) then …instrukcje kasujące… end if

      Reply
  2. Super porada 🙂
    Ja użyłem tego polecenia i wg mnie lepiej się sprawdza, bo lista kasuje się od razu po zaznaczeniu komórki „Kategoria”

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    lub też można tak:
    Range(„Kategoria”,”Podkategoria”).ClearContents
    i wtedy wszystko się wykasuje po zaznaczeniu komórki „Kategoria”

    Reply
    • Sławek, super!

      Reply

Submit a Comment

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

Pin It on Pinterest