Mam nadzieję, że poprzedni artykuł przypadł Ci do gustu.

… Jak to nie czytałeś poprzedniego artykułu?!

Koniecznie musisz to nadrobić zanim przejdziesz dalej!

W zeszłym tygodniu pokazałem Ci, czym są formularze użytkownika i jak wykorzystać je, by ustandaryzować, przyspieszyć i uprzyjemnić pracę z wprowadzaniem danych do arkusza. W tym tygodniu mam dla Ciebie dodatkową porcję wiedzy o formularzach. Abyś mógł ją w pełni wykorzystać, musisz zapoznać się z dwoma ostatnimi artykułami, ponieważ wyjaśniłem tam pewne instrukcje VBA, które będą Ci potrzebne do zrozumienia mechanizmów dzisiejszego przykładu.

Dziś chciałbym zapoznać Cię z alternatywą dla formularza użytkownika i pokazać Ci, jak stworzyć formularz wewnątrz arkusza kalkulacyjnego. Opiera się on na tak zwanych kontrolkach formatu ActiveX. Różnią się one w bardzo niewielkim stopniu od kontrolek dostępnych w formularzach użytkownika.

Formularz wewnątrz arkusza kalkulacyjnego – kontrolki ActiveX

Tworząc formularz bezpośrednio w arkuszu, zyskujesz dodatkowe korzyści:

Excel nie jest blokowany przez uruchomienie makra, więc użytkownik może w każdej chwili przejść do innego arkusza i go edytować,
 Masz szerszy wachlarz możliwości, jeśli chodzi o graficzną stronę tworzenia formularza – kształty, obrazy, wypełnienia deseniem, gradientem,
 Nie musisz tworzyć dodatkowych pól z listami (np.: gdybyś chciał umieścić tabelę lub listę danych na formularzu), możesz wykorzystać do tego komórki Excela,
Możesz stworzyć dużo czytelniejszy formularz. Masz więcej przestrzeni do dyspozycji, więcej opcji formatowania, możesz używać formatowania warunkowego (warunkowa wizualizacja bez konieczności programowania w VBA).

Musisz być jednak świadomy, że każde rozwiązanie ma zarówno blaski jak i cienie.

Formularze oparte na ActiveX mają dwie dosyć duże wady:

 Jeżeli nie zablokujesz możliwości edycji w arkuszu z formularzem, prędzej czy później ktoś przypadkowo zniweczy twoje dzieło. Pamiętaj, że edycja struktury (użytych kształtów, ich rozmieszczenia i formatowania) formularza jest bardzo prosta i dostępna dla wszystkich. Wystarczy podstawowa znajomość Excela. Musisz zabezpieczyć hasłem arkusz z formularzem.

 Brak blokady w postaci działającego makra jest tak samo zaletą jak i wadą. Z jednej strony użytkownik może podejrzeć inne arkusze lub przejść do nagłego pilnego zadania nie tracąc postępów w uzupełnianiu danych. Z drugiej strony może to powodować błędy i pominięcia. Za każdym razem, gdy coś oderwie użytkownika od formularza, zmniejsza się prawdopodobieństwo dokończenia wprowadzania danych i poprawności wykonania tej czynności.

Nie zniechęcaj się jednak potencjalnymi wadami. Miej to po prostu na uwadze, projektując formularz z wykorzystaniem Active X. W wielu przypadkach korzyści będą znacznie większe niż ewentualne niedociągnięcia.

Rejestr kart pomiarowych – przykład formularza wykorzystującego kontrolki ActiveX 

Przykład, jaki dla Ciebie przygotowałem to rejestr operacji kontrolno-pomiarowych wykonywanych przed działa jakości w firmie z branży automotive.

Czym są zatem dane wprowadzane do takie rejestru?

Przed rozpoczęciem produkcji kolejnego asortymentu na stanowisku roboczym musi dojść do przezbrojenia, które wiąże się z wykonaniem tak zwanych sztuk ustawczych, czyli próbnych wyrobów mających na celu sprawdzenie, czy maszyna została odpowiednio skalibrowana. Pracownicy kontroli jakości wykonują po zakończeniu przezbrojenia walidację w postaci PDS, czyli pierwszej dobrej sztuki. Na podstawie standardowego zestawu charakterystyk wraz z wymiarami nominalnymi i dopuszczalnymi tolerancjami, tworzona jest karta pomiarowa. Jeżeli wszystkie pomiary na takim dokumencie mają pozytywny wynik, stanowisko jest gotowe do pracy. Wymagania zintegrowanego systemu zarządzania jakością mówią o konieczności archiwizowania wyników tych pomiarów.

Opisywane przeze mnie narzędzie z powodzeniem działa we wspomnianej firmie już ponad pół roku. Pozwól, że poświęcę chwilę i opowiem Ci, jak wyglądała archiwizacja tych pomiarów, zanim to rozwiązanie zostało wdrożone.

Wyniki były spisywane w formie kart pomiarowych na bazie arkusza Excel. Za ich archiwizację odpowiedzialny był kontroler, który akurat wykonał pomiar. Daje nam to kilkunastu użytkowników, z których każdy dodawał do kart coś od siebie. W rezultacie baza kart pomiarowych stała się nieuporządkowanym tworem składającym się z wielu folderów rozmieszczonych w różnych miejscach na sieci wewnętrznej. W tych folderach znajdowały się dziesiątki plików, a w plikach dziesiątki zakładek z różnymi formami kart pomiarowych. Koniec końców stanąłem przed problemem sprowadzenia kilkudziesięciu różnych form archiwizacji do jednego prostego rejestru.

Jakie korzyści udało się osiągnąć?

 Ustandaryzowanie sposobu wprowadzania i przechowywania wyników pomiaru,

 Przyspieszenie pracy z wprowadzaniem danych (z ok. 15 minut do 1 minuty),

 Eliminacja błędów i pominięć przez zastosowanie mechanizmów sprawdzających kompletność danych i standardowe listy danych,

 Przyspieszenie i ułatwienie odnajdywania archiwalnych pomiarów,

 Możliwość prowadzenia statystyk dotyczących ilości przezbrojeń na danych maszynach w poszczególnych tygodniach, miesiącach, latach.

Jak widzisz było o co walczyć, ale dosyć o profitach płynących z tego rozwiązania. Przejdźmy teraz do samego formularza.

Powyższe zdjęcie to efekt finalny mojej pracy. Układ oraz formatowanie powyższych obiektów, nie ma żadnego znaczenia. Tak jak zawsze wspominam, kieruj się logiką przepływu danych i czytelnością. Wszystkie mechanizmy wykorzystane w opisywanym przykładzie wyjaśniłem za pomocą komentarzy wewnątrz edytora VBA. Chciałbym aby dzisiejszy artykuł miał maksimum treści, a minimum objętości, żebyś mógł jak najszybciej przejść do analizy przygotowanego pliku. Nie będę, więc dokładał tekstu opisami kodu. Pozostawiam go do przeanalizowania bezpośrednio w pliku.

Jak stworzyć formularz na bazie arkusza?

Wstawianie kontrolek ActiveX

Kształty MS Excel

Wstawianie kształtów

Edytowanie kształtów

 WAŻNE!

Pamiętaj, że rozmiary zdjęć i kształtów, domyślnie dostosowują swój rozmiar w momencie rozszerzania i zwężania komórek. Myśląc o formularzu chcesz, żeby aby jego elementy zachowały stałe wymiary i pozycję.

Użyj prawego przycisku myszy na jednym z obiektów i wybierz polecenie Formatuj kształt. Z panelu po prawej stronie wybrać kartę Opcje kształtu, następnie zakładkę Rozmiar i właściwości. Rozwiń grupę właściwości i wybierz opcję Nie przenoś ani nie zmieniaj rozmiaru z komórkami.

W oknie Właściwości możesz znaleźć wszystkie opcje dostępne na karcie Formatowania kształtów, więc jeżeli ten sposób edycji jest dla Ciebie wygodniejszy, to nie musisz się obawiać, że nie znajdziesz tam wszystkich narzędzi. Dodatkowo możesz tam zmieniać właściwości tekstowe i obszarowe, których nie znajdziesz na karcie, jak np.: marginesy, czy umieszczanie obiektu na wydruku.

Wyrównywanie kształtów

Dodawanie przycisków

Grupowanie kształtów

Kod VBA

Programowanie kontrolek ActiveX, niewiele różni się od programowania formularzy użytkownika. Wszystkie mechanizmy, stosowane w pliku z poprzedniego artykułu , zadziałają, więc nie powinieneś mieć problemu z ich zrozumieniem.

Wypełnianie pól danymi oraz wszelkie akcje, które mają zadziać się przed użyciem formularza, musimy zawrzeć w zdarzeniu Worksheet_Activate. Kod zawarty w tym zdarzeniu wykona się za każdym razem, gdy przejdziesz do arkusza.

Przejdź do Edytora VBA używając skrótu klawiszowego Alt + F11 lub polecenia Visual Basic w zakładce Deweloper. Z listy projektów wybierz poprzez dwukrotne kliknięcie arkusz, w którym zaprojektowałeś formularz. Z listy wybieranej w prawym górnym rogu, wybierz pozycję Worksheet, a następnie z listy obok, wybierz pozycję Activate.

Domyślnie zdarzenie po wybraniu pozycji z pierwszej listy to SelectionChange. Po tym jak wybierzesz pozycję Activate z drugiej listy, możesz usunąć wpis, z opcją domyślną.

Listy zależne

Pola opcji

Na formularzu znajdują się również dwie kontrolki CheckBox. Pola wyboru pozwalają nam na szybkie włączanie i wyłączanie opcji formularza. W naszym przykładzie dostępne opcje, to zapis pliku po wprowadzeniu danych, oraz czyszczenie formularza z danych po wprowadzeniu. Kod aktywujący te funkcje mieści się zaledwie w kliku linijkach i został wprowadzony do makra obsługiwanego przez przycisk Wprowadź.

Wszystkie mechanizmy zostały opisane wewnątrz Edytora VBA, więc nie pozostaje mi nic innego jak skierować Cię do przygotowanego przeze mnie pliku. Jeżeli przyswoiłeś treść dwóch poprzednich artykułów, zdecydowanie poradzisz sobie z opanowaniem kodu zawartego w tym narzędziu.

Już dziś zapraszam Cię na naszą stronę w przyszłą środę. Pokażę Ci, jak pisać własne funkcje, które dodadzą Twoim arkuszom jeszcze więcej możliwości.

Jak zwykle zachęcam Cię do wyrażania opinii w komentarzach. Napisz proszę, czy nauka VBA jest dla Ciebie wyzwaniem. Może udało Ci się już opracować jakieś narzędzia, którymi chciałbyś się pochwalić. Chętnie przeczytam, jaką wartość przyniósł Ci do tej pory nasz cykl artykułów.

Zobacz pozostałe artykuły dotyczące opracowania narzędzi Excel: