Czyli sterowanie zdarzeniem

To będzie chyba najkrótszy post na tym blogu. Niby trudna rzecz, a wymaga 2 linijek kodu VBA i ustawienia jednej opcji w Excelu. O co chodzi?

W tym wpisie pokazywałam jak zrobić listę wielokrotnego wyboru za pomocą narzędzia sprawdzania poprawności. Wszystko pięknie działało, natomiast problem pojawiał się wtedy, kiedy chcieliśmy dokonać zmiany we wcześniej wpisanych wartościach, np. skasować jakąś wartość. Efekt był taki, że ponieważ skorzystaliśmy z mechanizmu sprawdzania poprawności, to Excel nie pozwalał nam wprowadzać do komórki innych wartości, niż pojedynczy wpis na liście rozwijanej. A taką było większość wpisów, które wybraliśmy.

Dlatego dzisiaj pokażę rozwiązanie tego problemu. Najprostsze z możliwych, czyli takie, jak lubię :). Efekt będzie taki:

Do dzieła!

W tym najprostszym rozwiązaniu, które bezczelnie ściągnęłam od Microsoftu, wykorzystuję logikę trybu projektowania. Tryb projektowania jest to tryb, w którym możemy ustawiać wygląd i właściwości kontrolek, bez uruchamiania ich działania. W Excelu wykorzystujemy go np. gdy chcemy zmienić rozmiar pola tekstowego, wstawionego jako kontrolkę ActiveX (których zresztą nie polecam). W Wordzie, gdy tworzymy interaktywny formularz z wykorzystaniem formantów zawartości. Tryb ten jest też w Accessie.

Najlepiej będzie zrozumieć jego działanie i przydatność na przykładzie Power Pointa. Można powiedzieć, że tryb projektowania to ten tryb, w którym tworzymy prezentację. Tworzymy, czyli projektujemy, w nim wszystko: tekst, animacje, dźwięki, interaktywne przyciski i inne. Natomiast, żeby zobaczyć jak to wszystko działa – uruchamiamy prezentację, czyli wychodzimy z trybu projektowania.

Dokładnie to samo zrobimy w naszym przykładzie. Jak włączymy tryb projektowania – ja go tutaj nazwę tryb edycji, ponieważ lepiej mi ta nazwa pasuje do zagadnienia – będziemy mieli możliwość edycji komórki, w której chcemy dokonać zmian, np. usunąć niepotrzebny wpis.

Żeby jednak zadziałało to zgodnie z naszymi oczekiwaniami – trzeba będzie dokonać pewnej zmiany w utworzonych już wcześniej regułach sprawdzania poprawności, konkretnie w naszych listach. I od tego zaczniemy.

Sprawdzanie poprawności – niezbędna modyfikacja

Listy rozwijane są cudowne. Pamiętajmy jednak, że niezależnie od tego, jak cudnie wyglądają, nadal są sprawdzaniem poprawności, czyli służą do tego, aby kontrolować czy użytkownik nam nie wpisuje jakichś bzdur! Jak więc takie wpisze – Excel będzie na niego wrzeszczał, poprzez wyświetlenie takiego o to przyjemnego komunikatu (z dźwiękiem na prawdę można się go przestraszyć!):

Komunikat o niepoprawnych danych

Można go spersonalizować i wyświetlić inną treść, ale nieważne, ponieważ my i tak chcemy się go pozbyć. Dlaczego? Ponieważ jeśli wciąż się będzie pojawiał, nie dokonamy żadnych zmian, ponieważ nigdy te kilka wartości, które z listy wybieramy nie będą się równać jednej z dozwolonych wartości. To ograniczenie VBA cudownie omija (co zresztą było dla mnie zaskoczeniem, kiedy tworzyłam to rozwiązanie), natomiast użytkownik nie ma takiej szansy – jest sprawdzany przez Excela. Bardzo to mądre, w końcu to sprawdzanie poprawności i po to je stosujemy.

Nam natomiast ten komunikat będzie przeszkadzał, więc go wyłączymy. I to wszędzie, gdzie chcemy mieć możliwość edycji wpisów.

Aby go wyłączyć, należy:

1. zaznaczyć komórki z takim samym sprawdzaniem poprawności,

2. wejść do menu Dane/ Poprawność danych, do zakładki Komunikat o błędzie

3. Odznaczyć checkbox Pokazuj alerty po wprowadzeniu nieprawidłowych danych

Wyłączenie sprawdzania poprawności danych

De facto efekt będzie taki, jakbyśmy wyłączyli sprawdzanie poprawności, ale zachowali sobie mechanizm listy rozwijanej. O to chodziło.

Oczywiście należy się liczyć z tym, że od tej pory, jeśli wpiszemy tam jakąś bzdurną wartość (np. aslkdjaldjasl), to Excel się nie zbuntuje i ten wpis przyjmie. Ja natomiast bym to zaryzykowała, ponieważ gdybyśmy mieli się przed tym zabezpieczać – rozwiązanie byłoby nieco bardziej skomplikowane, a myślę, że jest to tutaj sztuką dla sztuki.

Ok. Jak już to mamy – możemy przejść do stworzenia kontrolki, pozwalającej nam wejść do trybu edycji.

Tryb edycji – tworzenie kontrolki

Kontrolką, którą mam na myśli może być sporo elementów. Ja jednak zdecydowałam się na checkbox, ponieważ wydaje mi się tutaj najprostszy. Jak jest zaznaczony – jesteśmy w trybie edycji, jak odznaczony – nie jesteśmy. Proste.

Wstawimy więc sobie checkbox. Potrzebna nam będzie do tego karta Deweloper na wstążce. Jeśli jej nie masz – tutaj opisywałam jak ją dodać.

Teraz w sekcji Formanty klikamy przycisk Wstaw, wybieramy nasz checkbox…

Wstawianie checkboxa

…i rysujemy go w wybranym miejscu arkusza. Ja zrobiłam to na górze, ale nie ma to znaczenia:

Checkbox

(Nazwę Pole wyboru 1 oczywiście można zmienić ;))

Większe znaczenie ma komórka, która będzie przechowywała informację o wyborze użytkownika. Dzięki niej będziemy mogli się zorientować, czy checkbox jest zaznaczony, czy nie. Aby tę komórkę ustalić, należy:

1. kliknąć prawym przyciskiem myszy na checkbox

2. z manu kontekstowego wybrać Formatuj formant

3. w zakładce Kontrolka należy wskazać wybraną komórkę

Tworzenie łącza komórki

Teraz, gdy checkbox będzie zaznaczony, w komórce tej pojawi się PRAWDA, a gdy będzie odznaczony – FAŁSZ. To nam już wystarczy, by przejść (wreszcie!) do VBA.

Kod VBA

A tutaj będą dosłownie 2,5 linijki.

Logika jest taka, że jeśli jesteśmy w trybie edycji, czyli checkbox jest zaznaczony (komórka D2 ma wartość PRAWDA), makro, które opisałam poprzednio ma po prostu nie zadziałać. Natomiast, gdy checkbox jest odznaczony – wszystko ma działać jak poprzednio.

To wymaga zdefiniowanie zmiennej, np. TrybEdycji, która będzie zmienną logiczną, czyli Boolean. Dopiszemy to w drugiej linijce poprzedniego kodu po przecinku (to jest to pół linijki;)):

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

A zaraz później, jeszcze zanim włączymy obsługę błędów, wpiszemy te 2 linijki:

 TrybEdycji = Range("D2").Value
 If TrybEdycji Then Exit Sub

Pierwsza pobiera wartość komórki D2 (w niej checkbox wpisuje swoją wartość PRAWDA/FAŁSZ).

Druga sprawdza, czy ta wartość jest prawdziwa. Jeśli tak – kończy tę procedurę.

Na szczęście wersja językowa Excela nie ma znaczenia. VBA cudownie radzi sobie z polskim PRAWDA/FAŁSZ odpowiednio je interpretując. Między innymi dlatego też zdecydowałam się na kontrolkę checkboxa;)

Wszystko w całości wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Wybor As String, PoprzedniaWartosc As String, NowaWartosc As String
 Dim ZakresSprPopr As Range, TrybEdycji As Boolean
 
 TrybEdycji = Range("D2").Value
 If TrybEdycji Then Exit Sub
 
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

Efekt wszystkiego najlepiej zobaczyć tutaj:

Albo na filmie, gdzie krok po kroku opisuję tworzenie tego rozwiązania: