Czy zastanawiałeś się kiedykolwiek nad możliwością stworzenia licznika, który będzie pokazywał użytkownikom aktualną informację odnośnie tego, ile razy został wydrukowany wybrany arkusz? Czy jest to w ogóle do zrobienia? Właśnie z takim pytaniem zwróciła się do mnie Anna, jedna z Czytelniczek bloga, a ponieważ temat wydał mi się bardzo ciekawy, dlatego postanowiłem go opisać i podzielić się nim również z Tobą.
Oto, jak brzmiało dokładnie pytanie Anny:
Jak więc widzisz rozwiązanie (o ile istnieje) powinno spełniać następujące wymogi:
- zlicza każde użycie polecenia Drukuj w Excelu i aktualizuje licznik,
- każdy arkusz ma swój oddzielny licznik, więc ich ilość będzie odpowiadała ilości utworzonych arkuszy,
- w każdym arkuszu licznik musi być widoczny, umieszczony na stałe,
- użytkownik nie ma możliwości wyzerowania lub zmodyfikowania licznika (jego wartości).
Wyzwanie
Po chwili zastanowienia doszedłem do wniosku, że mogę zaproponować Annie pewne rozwiązanie. 😊
Sposób przygotowania licznika zademonstruję na nowym skoroszycie, ale nic nie stoi na przeszkodzie, abyś zastosował je do już istniejącego pliku.
Aby osiągnąć zamierzony efekt końcowy będziemy musieli wykonać następujące kroki:
- Stworzyć nowy skoroszyt i dodać odpowiednią ilość arkuszy (lub otworzyć już istniejący).
- Utworzyć nazwy zdefiniowane, których użyjemy jako zmiennych do przechowywania bieżących wartości odnośnie liczby wykonanych wydruków.
- Napisać kod w języku VBA, który będzie odpowiedzialny za aktualizowanie zmiennych po każdym kliknięciu przycisku Drukuj. Jeśli nie jesteś mocny w pisaniu makr, to się nie przejmuj. Ten kod będzie naprawdę prosty.
- Wstawić nowy arkusz ZESTAWIENIE, w którym umieścimy w kolumnie A wszystkie utworzone liczniki.
- Wstawić kształt do jednego z arkuszy, a następnie przypisać do niego wartość licznika. Kształt ten odpowiednio sformatujemy, a następnie powielimy i umieścimy w pozostałych arkuszach.
- Ponieważ liczniki mają być niedostępne dla użytkowników, przed ich modyfikacją lub usunięciem, dlatego włączymy chronienie arkuszy, jednocześnie umożliwiając użytkownikom edycję samego arkusza.
- Na koniec trwale ukryjemy arkusz ZESTAWIENIE i zapiszemy cały projekt jako skoroszyt Excel z obsługą makr.
Spokojnie, niby dużo pracy jest przed nami, ale to naprawdę nie jest skomplikowane. Najlepiej będzie jak od razu zabierzemy się do roboty. 😊
Proponowane rozwiązanie
Jeżeli chcesz obejrzeć poradnik w formie video, to kliknij tutaj. W przeciwnym razie kontynuuj lekturę.
KROK 1: Tworzymy nowy skoroszyt i dodajemy interesującą nas liczbę arkuszy
Najszybciej nowy plik Excela utworzymy używając skrótu klawiaturowego Ctrl + N, który stworzy nową strukturę wraz z odpowiednią liczbą arkuszy. W zależności od tego w jaki sposób masz skonfigurowany program, to może być 1 arkusz, 3 arkusze lub każda inna ich ilość.
Jeżeli zastanawiasz się gdzie w Excelu możesz zdefiniować z jaką ilością arkuszy ma być tworzony każdy nowy skoroszyt, to kliknij na kartę Plik, a następnie Opcje. W oknie dialogowym Opcje programu Excel wyświetli się zakładka Ogólne. W prawej części tego okna znajdziesz w grupie Podczas tworzenia nowych skoroszytów opcję o nazwie Dołącz następującą liczbę arkuszy. Teraz wystarczy, że wprowadzisz tutaj pożądaną wartość i klikniesz przycisk OK. Od tej pory każdy nowy skoroszyt będzie zawierać wskazaną liczbę arkuszy.
KROK 2: Nazwy zdefiniowane
Jak zapewne wiesz nazwy zdefiniowane w Excelu służą przede wszystkim do nadawania nazw pojedynczym komórkom lub całym zakresom komórek. Dzięki temu łatwiej można konstruować formuły, bo dla przykładu zdecydowanie lepiej wygląda taki zapis =Kwota_netto*(1+VAT), aniżeli tradycyjne odwołania do komórek =B10*(1+$A$1).
Czy wiesz, że nazwy zdefiniowane mogą również przechowywać wartości? Tak, możesz dla przykładu utworzyć nazwę KursEUR i przypisać ją nie do komórki, ale do konkretnej wartości (stałej).
I w naszym przykładzie użyjemy właśnie tego drugiego zastosowania. Dla każdego z arkuszy utworzymy nazwę zdefiniowaną, np. licznikA1 dla Arkusz1, licznikA2 dla Arkusz2 itd., a następnie każdej z nich przypiszemy wartość początkową, czyli zero (0).
Aby zdefiniować nazwy wejdziemy na kartę Formuły, a w grupie Nazwy zdefiniowane wybierzemy polecenie Menedżer nazw. Moglibyśmy również kliknąć Definiuj nazwę.
W oknie dialogowym Menedżer nazw klikamy przycisk Nowy i pojawia się kolejne okno Nowa nazwa. Teraz w polu Nazwa podajemy nazwę licznika, a w polu Odwołuje się do wprowadzamy wartość zero. Po kliknięciu przycisku OK nowa nazwa pojawia się na liście.
Te same czynności powtarzamy tyle razy ile mamy arkuszy.
Ostatecznie okno Menedżera nazw prezentuje nam całą listę utworzonych nazw. Zamykamy okno.
KROK 3: Makro
Przyszedł czas na najtrudniejszy element naszej układanki, czyli napisanie makra. Jeżeli jesteś początkującym lub średniozaawansowanym użytkownikiem Excela, ale jeszcze nie miałeś do czynienia z tym tematem, to należy Ci się kilka słów wstępu.
Otóż makra to programy pisane w języku VBA (Visual Basic for Applications), które pozwalają zautomatyzować powtarzające się czynności, dzięki czemu użytkownicy Excela oszczędzają sporo czasu i mogą zająć się ciekawszymi zadaniami.
Makra można również nagrywać, natomiast bardziej zaawansowani użytkownicy piszą je samodzielnie, ponieważ oferuje to o wiele większe możliwości zarządzania Excelem i tym, co się dzieje z naszymi danymi.
Na potrzeby tego artykułu zakładam, że makr nie piszesz i nie nagrywasz. W takim przypadku będziesz musiał sprawdzić czy na wstążce Excel posiadasz kartę o nazwie Deweloper.
Jeżeli nie, to pierwszym zadaniem będzie jej aktywowanie. Najszybciej zrobimy to klikając prawym przyciskiem myszy na dowolną kartę na Wstążce Excela, a następnie wybierając polecenie Dostosuj Wstążkę.
W prawej części okna odszukaj kartę Deweloper, zaznacz ją klikając kwadracik umieszczony z lewej strony nazwy i kliknij przycisk OK. Brawo! Karta jest już na swoim miejscu. 😊
Teraz kliknij kartę Deweloper, przejdź do grupy Kod i wybierz polecenie Visual Basic, aby włączyć edytor Visual Basic. Możesz też użyć skrótu klawiaturowego Alt + F11.
Przyszedł czas na napisanie (a w Twoim przypadku przepisanie 😉) kodu makra. Całość będzie umieszczona w tym skoroszycie, dlatego na samym początku w oknie Project odnajdujemy nasz plik, a następnie dwukrotnie klikamy na Ten_skroszyt.
Po prawej stronie pojawi się okno Code, w którym wpiszemy instrukcje. Jeśli nie widzisz tego okna, użyj skrótu F7.
Teraz skopiuj poniższy kod i wklej go do edytora VBA w oknie Code.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim strLicznik As String
Select Case ActiveSheet.Name
Case Sheets(1).Name
AktualizujLicznik (“licznikA1”)
Case Sheets(2).Name
AktualizujLicznik (“licznikA2”)
Case Sheets(3).Name
AktualizujLicznik (“licznikA3”)
End Select
ThisWorkbook.Save
End Sub
Private Function AktualizujLicznik(strLicznik As String) As Long
Dim strValue As String
strValue = ActiveWorkbook.Names(strLicznik)
strValue = Replace(strValue, “=”, “”) + 1
ActiveWorkbook.Names(strLicznik).RefersTo = strValue
End Function
Nie wchodząc za bardzo w szczegóły mamy tutaj jedną procedurę Workbook_BeforePrint i jedną funkcję AktualizujLicznik.
Pierwsza z nich jest wywoływana zawsze wtedy, kiedy użytkownik kliknie przycisk Drukuj. To powoduje sprawdzenie, który z arkuszy jest aktywny, a następnie przekazanie do funkcji AktualizujLicznik nazwę zdefiniowaną utworzoną dla konkretnego arkusza.
Druga z nich po otrzymaniu nazwy zdefiniowanej pobiera przypisaną do niej wartość, która na samym początku wynosi „=0”. Zadaniem funkcji jest zwiększanie tej wartości o 1 za każdym razem, kiedy zostanie wywołana. Ponieważ wartość przypisana do nazwy zdefiniowanej jest łańcuchem tekstowym (zawiera znak = i wartość liczbową), dlatego w drugim kroku znak ten jest usuwany, a wartość liczbowa zwiększana o 1 i ponownie przypisywana do nazwy.
Powyższy kod odpowiada za prawidłowe aktualizowanie stanów poszczególnych liczników.
KROK 4: Arkusz ZESTAWIENIE
Powracamy do naszego skoroszytu przełączając okna lub używając ponownie skrótu Alt + F11. Następnie wstawiamy nowy arkusz o nazwie ZESTAWIENIE i w kolumnie A umieszczamy po kolei odwołanie do utworzonych nazw liczników. Dla przykładu w komórce A1 wprowadzamy znak równości (=) i wpisujemy licznikA1. W komórce A2 będzie licznikA2, a w komórce A3 licznikA3.
Arkusz ten przyda nam się w kolejnym kroku.
KROK 5: Kształt
Ponieważ Anna chce umieścić licznik na stałe w każdym z arkuszy i nie może on być modyfikowany, dlatego teraz zajmiemy się jego przygotowaniem.
Przechodzimy do Arkusza1, a następnie na karcie Wstawianie w grupie Ilustracje klikamy Kształty i wybieramy dowolny kształt, np. Owal. Teraz klikamy w dowolnym miejscu bieżącego arkusza, aby wstawić kształt.
Następnie przypisujemy do niego wartość odpowiedniego licznika. Jak to robimy?
Mając zaznaczony kształt, klikamy na pasek formuły wstawiamy znak równości (=), a następnie przechodzimy do arkusza ZESTAWIENIE, wskazujemy komórkę A1 i zatwierdzamy klawiszem Enter. Jak zauważysz zawiera on teraz wartość 0. Jedyne czego jeszcze potrzebujemy to sformatować wygląd kształtu według naszych potrzeb.
Po przygotowaniu kształtu w Arkuszu1 powtórz wszystkie czynności dla pozostałych arkuszy. Upewnij się, że każdy z kształtów jest prawidłowo przypisany do wartości w arkuszu ZESTAWIENIE.
KROK 6: Ochrona
Każdy nowo utworzony arkusz jest w pełni dostępny dla użytkownika, co oznacza, że może on go dowolnie modyfikować, wprowadzać wartości, usuwać je, przekształcać itp.
Anna chce dać użytkownikowi możliwość pracy z arkuszem, ale jednocześnie nie chce, aby mógł modyfikować liczniki i prezentowane w nich wartości. Dlatego będziemy musieli włączyć chronienie wszystkich arkuszy z licznikami.
Zanim jednak to uczynimy zakładam, że chcemy umożliwić pracę z wszystkimi komórkami arkusza. W tym celu musimy je odblokować. Żeby nie powtarzać tej czynności tyle razy ile mamy arkuszy, zaznaczymy je wszystkie od razu, a następnie wyłączymy blokowanie komórek.
Naciskamy i przytrzymujemy klawisz Ctrl i klikamy na karty wszystkich interesujących nas arkuszy (Arkusz1, Arkusz2, Arkusz3). Klikamy prawym przyciskiem myszy na dowolnej komórce i z menu wybieramy opcję Formatuj komórki lub używamy skrótu klawiaturowego Ctrl + 1.
W oknie dialogowym Formatowanie komórek przechodzimy na kartę Ochrona, odznaczamy opcję Zablokuj i klikamy przycisk OK. Następnie klikając prawym przyciskiem myszy na karcie arkusza wybieramy opcję Rozgrupuj arkusze.
W tym momencie możemy zająć się włączaniem ochrony arkusza. Wystarczy, że wejdziesz na kartę Recenzja i w grupie Zmiany klikniesz polecenie Chroń arkusz.
W oknie dialogowym Chronienie arkusza podajesz hasło (potem będziesz musiał je potwierdzić) i zaznaczasz wszystkie opcje poza jedną – Edytowanie obiektów. Dzięki temu nie będzie można naszego kształtu usunąć, jak również nie będzie można zmodyfikować nazw zdefiniowanych.
Uwaga! Chronienie arkusza niestety nie zabezpiecza nas całkowicie przed niepowołaną zmianą nazw zdefiniowanych. Wystarczy, że użytkownik doda nowy arkusz, który nie jest chroniony, i mając go aktywnego może wywołać Menedżera scenariuszy.
Czy można temu zaradzić? W pewnym sensie tak. Wystarczy, że włączymy ochronę skoroszytu, która uniemożliwia np. dodanie nowego arkusza. Ponieważ Anna nie określiła czy skoroszyt będzie mógł mieć zmienną strukturę, dlatego przyjmuję, że liczba arkuszy będzie stała i włączam ochronę skoroszytu (karta Recenzja > Zmiany > Chroń skoroszyt).
KROK 7: Trwale ukryj arkusz i kod makra
Wszystkie utworzone liczniki odwołują się do komórek w arkuszu ZESTAWIENIE. Ponieważ ma on charakter pomocniczy i nie ma potrzeby, aby był widoczny cały czas, dlatego możemy go ukryć.
W tym celu wystarczy, że klikniesz na jego karcie prawym przyciskiem myszy i wybierzesz opcję Ukryj. Jeśli wcześniej włączyłeś ochronę skoroszytu (krok 6), to opcja będzie nieaktywna. Dlatego na chwilę będziesz musiał wyłączyć ochronę skoroszytu, ukryć arkusz, a następnie włączyć ją ponownie.
Jeżeli nie chcemy, aby niepowołany użytkownik „grzebał” nam w kodzie VBA, to powinniśmy również zablokować dostęp do projektu z poziomu edytora VBA.
Jeszcze raz używamy skrótu Alt + F11 i przechodzimy do edytora VBA. Następnie prawym przyciskiem myszy możemy kliknąć na Ten_skoroszyt i z menu podręcznego wybrać opcję VBA Project Properties.
W oknie dialogowym VBAProject – Project Properties wybieramy drugą zakładkę Protection. Potem zaznaczamy opcję Lock project for viewing i w polach Password i Confirm password wprowadzamy hasło, a następnie klikamy przycisk OK.
Wracamy do Excela i zapisujemy cały skoroszyt jako Skoroszyt programu Excel z obłsugą makr (*.xlsm) i gotowe! 😊
Poradnik wideo
Podsumowanie
W ten oto sposób dobrnęliśmy do końca naszej przygody, dzięki której Anna będzie miała możliwość śledzenia ile razy każdy z arkuszy został wydrukowany. Zapewne nie jest to jedno jedyne rozwiązanie problemu jaki przedstawiła Czytelniczka bloga, ale jest to opcja w pełni działająca, która pokazuje również, jak można połączyć ze sobą kilka narzędzi w Excelu, aby uzyskać zamierzony efekt.
Pobierz plik: Ile razy wydrukowano konkretny arkusz
Mam nadzieję, że i Tobie ten artykuł w jakiś sposób się przysłuży. Być może masz inną propozycję rozwiązania tego zadania? Jeśli tak, to nie wahaj się podzielić się swoją wiedzą w komentarzach pod tym wpisem.