fbpx

Lista rozwijana wielokrotnego wyboru (odsłona 2)

09.01.2018 | ECP3, Makra VBA

Czyli wybieranie z listy ROZWIJANEJ więcej niż jednej pozycji

W poprzednim wpisie pokazywałam sposób na stworzenie listy wielokrotnego wyboru. Lista ta była formantem formularza, czyli „pływającym” w arkuszu obiektem, który mogliśmy umieścić w wybranym przez siebie miejscu. Rozwiązanie to było świetne, gdy mieliśmy mało komórek, do których chcieliśmy wpisać wartość z tej listy. Gorzej jest jednak, gdy mamy wiele komórek, w której, o zgrozo!, każda ma inną listę i z każdej z nich chcemy wybierać po kilka wartości do komórki. Masakra!

Sytuacje takie jednak jak najbardziej się zdarzają, więc dziś o tym, jak sobie wtedy radzić. Znów będzie o VBA i to nie takim oczywistym niestety. I znów będą zdarzenia :). Znów, ponieważ już ostatnio o nich pisałam, np. przy otwieraniu pliku na konkretnym arkuszu czy kasowaniu wpisu na zależnej liście rozwijanej.

Efekt będzie taki:

Wynik

A formatka taka:

Formatka

Formatka

Mamy więc dwa pola, w którym każde ma listę rozwijaną. Dla czytelności wpisu zrobiłam tylko 2 listy. Natomiast jeśli potrzebujesz – możesz mieć ich 5 czy 100 – piękno tego rozwiązania polega na tym, że nie ma to kompletnie żadnego znaczenia :).

O tym, jak zrobić listy rozwijane pisałam tutaj, a jak zrobić dynamiczne listy rozwijane – tutaj.

Założenia

Ogólnie chcemy osiągnąć efekt listy rozwijanej wielokrotnego wyboru przy jak najmniejszym nakładzie pracy. Wykorzystamy więc mechanizm sprawdzania poprawności danych. I może Cię zmartwię, ale ten mechanizm nie oferuje możliwości wielokrotnego zaznaczania. W sumie, to tylko lista z poprzedniego wpisu oferuje taką możliwość. Musimy więc poradzić sobie inaczej. Co najśmieszniejsze, w ogóle nie będziemy tykać list rozwijanych! Skupimy się na wartościach komórki i tym, czy ona w ogóle ma w sobie listę rozwijaną, a konkretnie sprawdzanie poprawności z listą rozwijaną. Do tego koniecznie musimy zrozumieć dwa założenia:

  1. Uwzględniane będą tylko komórki, które mają sprawdzanie poprawności o typie Lista (komórki bez sprawdzania poprawności lub z innym typem sprawdzania nie będą brane pod uwagę)
  2. Po wyborze z listy rozwijanej, wybrana wartość zostać dopisana w nowym wierszu tej komórki

Niby to takie oczywiste, ale koniecznie musimy sobie zdawać z tego sprawę, aby zrozumieć kod VBA, który omówimy za chwilę. Cały trik tego rozwiązania polega na tym, że makro będzie dopisywało najnowszą wartość z listy do poprzedniej wartości komórki.

Warto też wiedzieć, jak w Excelu można zaznaczyć komórki, które mają sprawdzanie poprawności. Służy do tego polecenie Przejdź do…/Specjalnie, które można wywołać np. z menu Narzędzia główne/Znajdź i zaznacz/Przejdź do – specjalnie:

Przejdź do... specjalnie

Przejdź do… specjalnie

Pojawi się wtedy okienko, z którego wybieramy Sprawdzanie poprawności danych (to samo można osiągnąć skrótem klawiszowym Ctrl + g lub F5):

Zaznaczanie komórek ze sprawdzaniem poprawności

Zaznaczanie komórek ze sprawdzaniem poprawności

Excel wtedy zaznaczy nam tylko komórki, które zawierają sprawdzanie poprawności. Tę opcję wykorzystamy w kodzie, ponieważ jakoś musimy sprawdzić, czy które komórki mają owe sprawdzanie poprawności. Na naszej formatce zaznaczy więc zakres C2:C3.

Jak już to wiemy – omówmy sobie logikę naszego zadania.

Logika

Tak, jak wspomniałam na wstępie, nie będziemy ruszać list rozwijanych. Zajmiemy się tylko wartościami komórek.

Nasze makro uruchomi się, jak wykryje zmianę wartości komórki (zdarzenie Change). Pobierze wtedy wartość obecnie wpisaną do komórki (i zapisze ją w zmiennej oczywiście). Problem jednak jest w tym, że poprzednia wartość została właśnie skasowana, gdy user wybrał nową wartość na liście. Tragedii nie ma na szczęście, ponieważ tę operację można cofnąć i tak też zrobimy. Pojawi się wtedy poprzednia wartość, którą też zapiszemy do zmiennej. Jak już mamy jedno i drugie to teraz tylko wpisać obie, odpowiednio połączone do komórki.

Główna logika jest właśnie taka. Niestety, jak za chwilkę zobaczysz, kod będzie musiał poradzić sobie jeszcze z kilkoma innymi rzeczami, które będą w tej logice przeszkadzać. Będą to błędy, które mogą wyskoczyć po drodze, sam fakt tego, że w arkuszu działają zdarzenia (podczas cofania operacji też zmieniamy wartość komórki, więc zdarzenia będzie się chciało ponownie uruchomić!). No i też należy pamiętać o sytuacji, gdy wybieramy pierwszą wartość z listy. Na to wszystko nasz kod będzie musiał być odporny.

Kod VBA

Tak jak wspominałam wcześniej – będziemy działać na zdarzeniu. Konkretnie na zdarzeniu Change. Czyli jeśli wartość jakiejś komórki się zmieni – makro się uruchomi. Zmianą komórki jest oczywiście zmiana jej wartości.

Aby dodać zdarzenie do arkusza, wchodzimy do VBE (Alt + F11) i klikamy dwa razy na naszym arkuszu w okienku Project Explorer. Następnie z lewej listy rozwijanej modułu prywatnego arkusza wybieramy Worksheet, a z prawej: Change. Kroki te dokładnie omówiłam tutaj.

Ok. Cały kod umieścimy w procedurze Worksheet_Change.

Najpierw deklarujemy zmienne. Potrzebne nam będą 3 zmienne tekstowe: dla wartości poprzednio wpisanej do komórki (PoprzedniaWartosc), dla nowej (NowaWartosc) i dla tej wybranej z listy (Wybor). Przyda się jeszcze zmienna, która będzie zakresem wszystkich komórek, które mają sprawdzanie poprawności (ZakresSprPopr). A w kodzie wygląda to tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PoprzedniaWartosc As String, NowaWartosc As String, Wybor As String
    Dim ZakresSprPopr As Range
    
End Sub

Jeśli ten kod ma być zaimplementowany w arkuszu, w którym istnieje choć cień szansy, że nie będzie żadnego sprawdzania poprawności, należy już na samym początku zabezpieczyć się przed brzydkim, szarym komunikatem, który Excel wyrzuci nam, gdy będziemy próbowali przypisać nieistniejący zakres do zmiennej ZakresSprPopr. Konieczna jest więc tutaj malutka obsługa błędów, która, jak tylko błąd się pojawi, przekieruje nas na sam koniec procedury. Obsługa błędów to polecenie:

On Error GoTo Obsluga

Przy czym Obsluga pojawi się na końcu kodu i jest to miejsce, gdzie będziemy przechodzić (GoTo), gdy błąd się wydarzy.

A teraz już ciekawsza część :). Pobieramy teraz wartość komórki, która się zmieniła (Target) i przypisujemy do zmiennej Wybor. Potem badamy, jaki zakres w arkuszu ma w sobie sprawdzanie poprawności (ZakresSprPopr), a następnie sprawdzamy, czy nasza komórka znajduje się w tym zakresie (czyli, czy ona sama ma sprawdzanie poprawności). Do tego służy polecenie Intersect. Jeśli te dwa zakresy nie będą miały części wspólnej, lub nasza komórka jest pusta (czyli właśnie ktoś skasował wartość z niej) – kończymy procedurę:

    Wybor = Target.Value
    
    Set ZakresSprPopr = Cells.SpecialCells(xlCellTypeAllValidation)
    If Intersect(Target, ZakresSprPopr) Is Nothing Or Wybor = "" Then Exit Sub

No ok. Ale co ma się stać, jeśli wszystko jest ok, czyli jeśli nasza komórka ma sprawdzanie poprawności i jeśli ktoś wpisać do niej wartość? No po pierwsze warto sprawdzić, czy to sprawdzanie poprawności, które komórka posiada to lista rozwijana (czyli typ walidacji to 3). Jeśli tak – działamy dalej, czyli będziemy dopisywać wartości do komórki. Jeśli nie – w komórce ma zostać wpisana ta wartość, która tam była, czyli nic nie ruszamy. To załatwimy prostym if-em.

Dobrze, co natomiast, gdy w naszej komórce jest dobra lista? Wtedy działamy z naszą logiką. Żeby jednak wszystko pięknie poszło – musimy wyłączyć działanie zdarzeń. Dlaczego? Żeby nam się Excel nie zapętlił biedaczek :). Teraz możemy cofnąć operację (czyli de facto zmienić wartość komórki na poprzednią – i tutaj byłoby to zapętlenie), a potem zapamiętać poprzednią wartość komórki:

    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        Application.Undo
        PoprzedniaWartosc = Target.Value
    End If

Pytanie tylko, jaka ta poprzednia wartość jest. Bo jeśli jest żadna, czyli komórka była pusta (bo jest to pierwszy wybór z listy), to chcemy, aby docelowa wartość była identyczna jak ta, którą wybraliśmy z listy. Jeśli jednak była inna – będziemy łączyć obie te wartości, przy czym druga ma być w nowym wierszu. Poniższy kod wpisz w poprzednim if-ie:

 If PoprzedniaWartosc = "" Then
     Target.Value = Wybor
 Else
     NowaWartosc = PoprzedniaWartosc & vbNewLine & Wybor
     Target.Value = NowaWartosc
 End If

Na koniec już tylko dokończyć obsługę błędów, czyli:

Obsluga:
    Application.EnableEvents = True

Cały kod wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PoprzedniaWartosc As String, NowaWartosc As String, Wybor As String
    Dim ZakresSprPopr As Range

On Error GoTo Obsluga
    Wybor = Target.Value
    
    Set ZakresSprPopr = Cells.SpecialCells(xlCellTypeAllValidation)
    If Intersect(Target, ZakresSprPopr) Is Nothing Or Wybor = "" Then Exit Sub
    
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        Application.Undo
        PoprzedniaWartosc = Target.Value
        
        If PoprzedniaWartosc = "" Then
            Target.Value = Wybor
        Else
            NowaWartosc = PoprzedniaWartosc & vbNewLine & Wybor
            Target.Value = NowaWartosc
        End If
    End If
Obsluga:
    Application.EnableEvents = True
    
End Sub

I pięknie śmiga. Zobaczcie:

Wynik

Wynik

 

Jedyny problem jest taki, że jeśli się pomyliliśmy, to klapa. Trzeba skasować wartość komórki i wybrać wszystko od początku. Pojawią się bowiem dwa problemy:

  1. tabliczka sprawdzania poprawności, że wartość komórki jest nieprawidłowa. Z tym akurat nawet bez kodu łatwo sobie poradzić, jednak, gdy to zrobimy, pojawi się problem nr 2:
  2. wartości w komórce się powtórzą, ponieważ na każdą zmianę w komórce, Excel będzie uruchamiał przed chwilą napisane makro…

Można sobie z tym poradzić, ale o tym jeden z kolejnych wpisów. Jak w serialu normalnie ;).

A tymczasem wersja wideo:

 

I plik do pobrania (pamiętaj o włączeniu makr!:
MalinowyExcel Lista rozwijana wielokrotnego wyboru dw.xlsm

 

 

 

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

17 komentarzy

  1. A jak będzie wyglądał kod w sytuacji gdy chcemy, by wielokrotny wybór był tylko dla konkretnej kolumny w tabeli, a w pozostałych polach chcemy mieć standardową listę jednokrotnego wyboru?

    Reply
    • Na pewno jest – napiszę o tym wpis na blogu 🙂

      Reply
      • Dołączam się do pytania Pani Patrycji. Jak zrobić aby lista wielokrotnego wyboru dotyczyła tylko jednej kolumny/wiersza ?

        Reply
        • OK 🙂

          Reply
          • Mam ten sam problem 🙁 stworzyłam formularz i chciałabym aby wielokrotny wybór dotyczył tylko kilku komórek, reszta jednokrotny i inne, nie znalazłam odpowiedzi na blogu.

            Sam bog najlepszy na jaki trafiłam, brawo.

          • poradziłam, niestety napotkałam kolejny problem dotyczący zabezpieczonego arkusza

          • Jeśli chodzi o miejsce, gdzie ma działać zdarzenie – trzeba zastosować obiekt Intersect. Zobacz tutaj: https://malinowyexcel.pl/przeszukiwalna-lista-wyboru-z-podpowiedziami-formularz-vba/

            Odnośnie zabezpieczonego arkusz – trzeba zdjąć ochronę arkusza Arkusz.Unprotect „hasło”. A potem koniecznie założyć 🙂

            Ania – bardzo się cieszę, że blog Ci się przydaje 🙂

  2. lista wielokrotnego wyboru NIE DZIAŁA gdy włączamy chronienie arkusza/skoroszytu (można jedynie dokonać jednokrotnego wyboru), proszę o pomoc

    Reply
    • Hej,
      tak, faktycznie nie działa przy włączeniu ochrony arkusza, ponieważ procedura korzysta z metody SpecialCells, której nie można użyć w chronionym arkuszu. Wyjść z tej sytuacji może być kilka. Jedno z nich to np. zdjęcie ochrony arkusza na czas działania makra, a potem przywrócenie jej:
      – zdjęcie: Ark.Unprotect „HASLO”
      – przywrócenie: Ark.Protect „HASLO”
      Jeśli jest hasło ochrony, to trzeba je wpisać (HASLO).
      Trzeba jedna szczególnie uważać z takimi operacjami, aby w przypadku błędu na pewno ochrona z powrotem została założona!

      Reply
      • Jest jakieś inne wyjście z tej sytuacji ?

        Reply
      • Niestety zdjęcie i przywrócenie ochrony nie działa :/

        Reply
  3. Hej,

    Potrzebuję w excelu zrobić dynamiczne listy wielokrotnego wyboru.
    Z dynamicznością listy pomógł mi jeden z Twoich filmików – dzięki wielkie 🙂 (zastosowałam nazwę tabeli jako źródło danych).
    Jednak tu dla makra pojawia mi się Run-time error '424′ Object required. Gdy wybieram 'debug’ podświetla mi wiersz:
    If Target.Validation.Type = 3 Then

    Jak sobie z tym poradzić, co jest nie tak? (VB dopiero poziom początkujący).

    Makro (bez obsługi błędu), które stosuje – jest 1:1 z tym co przedstawiasz w filmiku.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Wybor As String, PoprzedniaWartosc As String, NowaWartosc As String
    Dim ZakresSprPopr As Range

    Wybor = Target.Value

    Set ZakresSprPopr = Cells.SpecialCells(xlCellTypeAllValidation)
    If Intersect(Target, ZakresSprPopr) Is Nothing Or Wybor = „” Then Exit Sub

    If Terget.Validation.Type = 3 Then
    Application.EnableEvents = False
    Application.Undo
    PoprzedniaWartosc = Target.Value

    If PoprzedniaWartosc = „” Then
    Target.Value = Wybor
    Else
    NowaWartosc = PoprzedniaWartosc & vbNewLine & Wybor
    Target.Value = NowaWartosc

    End If

    End If
    Application.EnableEvents = True

    End Sub

    Reply
    • Hej, błąd ten wskazuje, że coś mu się nie podoba z listą rozwijaną. Nie wiem, czy tylko literówka we wklejonym przez Ciebie kodzie, czy tak jest w makrze, ale powinno być tak: If TArget.Validation.Type = 3 Then zamiast If TErget.Validation.Type = 3 Then. Czyli A zamiast E. O to chodzi?

      Reply
  4. Dzień dobry,
    Jakiego kodu należy użyć aby wstawić „separatory” – np. kropkę lub przecinek, na końcu każdego wiersza, po każdej nowej wybranej wartości.

    Reply
    • Myślę, że można byłoby to zrobić np tak: NowaWartosc = PoprzedniaWartosc &”,” & vbNewLine & Wybor
      🙂

      Reply
      • Hej, a jakby to bylo gdyby kolejne wyniki mialybybyc pokazywane w kolejnych komorkach? No lista jest w a1, wynik 1szy w b1,c1 itd itd

        Reply
        • Jeśli dobrze rozumiem, to jako miejsce docelowe dal wybranych danych trzeba byłoby ustawić odpowiednie komórki w prawo. Czyli liczyć kolumny zakresu utworzonego przez te komórki i wpisywać do kolejnej kolumny w tym samym wierszu 🙂

          Reply

Submit a Comment

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

Pin It on Pinterest