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:

A formatka taka:

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

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

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

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: