Ukrywanie obiektów Excela

Jedną z metod ochrony pliku Excela przed nieuprawnionym działaniem użytkowników jest ukrywanie niektórych obiektów.
Jest tu kilka możliwości:

    • ukrycie kart arkuszy (zakładek)
      karta Plik –> Zaawansowanie –> Pokaż karty arkuszy
      Po odznaczeniu tej opcji nazwy kart arkuszy

będą niewidoczne

 

    • ukrycie arkuszy
      konkretne arkusze można ukryć. Wystarczy zaznaczyć jeden lub kilka arkuszy, a następnie po kliknięciu prawym przyciskiem myszy wybrać Ukryj z menu podręcznego

      Nie da się ukryć  wszystkich arkuszy – co najmniej jeden musi zostać odkryty.
      Odkrywanie arkuszy odbywa się bardzo podobnie – kliknięciem prawy przyciskiem myszy i wybraniem z menu podręcznego Odkryj…
       Pokaże się formularz, w którym widoczne będą wszystkie ukryte arkusze. Zaznaczając arkusz z listy (tu niestety trzeba to zrobić oddzielnie dla każdego arkusza) i naciskając OK – odkrywamy go.

    • ukrycie arkuszy kodem VBA
      arkusze mogą zostać także ukryte “głęboko”, tak, aby nie można było ich w prosty sposób odkryć. Jest to możliwe w kodzie VBA z wykorzystaniem właściwości Visible arkusza. Do wyboru jest tu:

        • xlSheetHidden
          arkusz zostanie ukryty, tak jak po ukryciu w menu podręcznym
        • xlSheetVeryHidden
          arkusz zostanie głęboko ukryty. Nie będzie widoczny na liście arkuszy do odkrycia w formularzu menu podręcznego
        • xlSheetVisible
          odkrycie arkusza

Przykładowa procedura ukrywająca arkusz może wyglądać tak:

Sub UkryjArkusz()
Worksheets(“Arkusz1”).Visible = xlSheetVeryHidden
End Sub

Odkrycie arkusza wymaga również procedury VBA przypisującej Visible=xlSheetVisible

    • ukrycie paska formuły/nagłówków/linii siatki
      domyślnie widoczne, można ukryć w karcie Widok–>Pokazywanie, odhaczając poszczególne opcje:

przy braku zaznaczenia tych opcji widok będzie wyglądać tak:

    • ukrycie formuł komórki
      jeżeli w komórce znajduje się formuła wynik jej działania jest widoczny w komórce, a sama formuła – na  pasku formuł nad nagłówkami kolumn.

Chcąc ukryć zapis tej formuły, przechodzimy do formatowania komórki i w zakładce Ochrona zaznaczamy opcję Ukryj.

Po włączeniu ochrony arkusza zapisana w komórce formuła jest niewidoczna (choć sam wynik jej działania nadal jest widoczny w komórce):

 

 

Poprawność danych w Excelu

Wymuszanie poprawności danych w Excelu jest jedną z metod zabezpieczania pliku Excel przed nieuprawnioną ingerencją i wpisywaniem błędnych danych, generujących dalsze błędy np. w formułach.
Ograniczenia wprowadzanych danych definiujemy na karcie Dane –> Narzędzia danych –> Poprawność danych

Zaznaczamy komórkę lub zakres komórek, w których chcemy wprowadzić ograniczenie, a następnie klikamy przycisk Poprawność danych. Pojawi się nowy formularz:

Zakładka Ustawienia

Kolejny krok to wybranie z listy kryterium poprawności dla zaznaczonych komórek.
Jest tu kilka możliwości:

    • Dowolna wartość
      – ustawienie domyślne, bez ograniczeń. To opcja, którą należy wybrać przy zdejmowaniu ograniczeń.
    • Pełna liczba
      wartości liczbowe będące liczbą całkowitą.
      Wprowadzane wartości danych można dodatkowo zdefiniować do określonego przedziału liczbowego. Możemy być on wyznaczony na sztywno (czyli z konkretnymi wartościami liczbowymi) lub jako odwołanie do wartości innych komórek.
    • Dziesiętne
      zasady identyczne jak w przypadku Pełna liczba, ale bez ograniczenia do liczb całkowitych.
    • Lista
      tu odwołam się do mojej wcześniejszej notki
      Lista rozwijalna
      gdzie szczegółowo opisałam temat, również w aspekcie tworzenia list dynamicznych
    • Data
      w komórkę musimy wpisać datę (również może być ograniczenie wartości do określonego przedziału dat).
      I na wszelki wypadek przypominam, że w Excelu
      Data to liczba
    • Godzina
      chcąc zadeklarować wpisywanie czasu, również musimy pamiętać, że to liczba, choć w odróżnieniu do daty – ułamkowa.
      Warto też zerknąć tu:
      Czas w Excelu
    • Długość tekstu
      definiujemy ilość znaków jaka musi być w komórce. Jeśli wpiszemy np. większa niż 3 – nie da się wpisać “tak”.
      Warto pamiętać, że spacja też jest znakiem.
    • Niestandardowe
      w tej opcji poprawność danych w komórce definiujemy sami za pomocą formuły, mamy więc możliwość wprowadzenia bardziej złożonych kryteriów.
      Np. dla komórki A1:
      =Czy.Tekst(A1) – ogranicza wprowadzanie danych tylko dla tekstu
      =MOD(A1;10) – wprowadzane tylko wartości podzielnych przez 10
      W podobny sposób można tu zdefiniować inne zasady.

Zakładka Komunikat wejściowy

Domyślnie – jest pusty. Po wypełnieniu pól komunikatu pojawi się on po aktywacji komórki.

Zakładka Alert o błędzie

Wypełnienie pól komunikatu powoduje, że przy wpisaniu wartości niezgodnej z ustawieniami wyświetli się komunikat, a sama wartość nie zostanie wprowadzona do komórki.

Kurs Excel 2010 esencja

Plik Excel tylko do odczytu

Zapisywanie pliku Excel tylko do odczytu jest najprostszym sposobem na udostępnienie pliku bez możliwości wprowadzania (a w zasadzie zapisywania) zmian. Oczywiście można taki plik edytować, ale wymaga to podania hasła.

Plik w trybie do odczytu zapisujemy korzystając z opcji Zapisz jako… i korzystając z Narzędzi.

Po kliknięciu w Narzędzia  i wybraniu Opcje ogólne… pojawi się nowy formularz:

Wpisując Hasło ochrony przed zmianami, potem potwierdzając je, spowodujemy, że przy następnym otwarciu pliku pojawi się monit o wpisanie hasła lub plik zostanie otwarty tylko do odczytu.

Jeżeli przy zapisywaniu hasła w opcjach ogólnych zaznaczymy także checkbox Zalecane tylko do odczytu, wyświetlony komunikat będzie wyglądać tak:

Po wpisaniu i potwierdzeniu hasła wystarczy zapisać plik i od tej pory chcąc go otworzyć – konieczne będzie podanie hasła.

Operacja odwrotna czyli odblokowanie tak zabezpieczonego pliku wymaga ponownego zapisania pliku z wyczyszczonym polem na hasło.

 

 

Otwieranie pliku hasłem

Najprostszym zabezpieczeniem pliku Excel przed dostępem osób nieuprawnionych jest po prostu zapisanie go z hasłem.
Karta Plik –> Informacje –> przycisk Uprawnienia

Po wybraniu opcji Szyfruj przy użyciu hasła pojawi się nowy formularz do wpisania hasła ochrony pliku.

Po wpisaniu i potwierdzeniu hasła wystarczy zapisać plik i od tej pory chcąc go otworzyć – konieczne będzie podanie hasła.

Operacja odwrotna czyli odblokowanie tak zabezpieczonego pliku wymaga ponownego wejścia do
Plik –> Informacje –> Uprawnienia –> Szyfruj przy użyciu hasła
i wyczyszczenie pola na hasło.

 

 

Kurs Excel dla księgowych

 

Ukrycie kodu VBA

Jak zabezpieczyć kod VBA w pliku Excela?  Jest taka możliwość w edytorze VBA. Wystarczy kliknąć prawym przyciskiem myszy projekt VBA i z menu podręcznego wybrać właściwości projektu VBA:

Otworzy się w ten sposób formularz, w którym przechodzimy do zakładki Protection.

Checkbox Lock Project for viewing jest domyślnie odhaczony. Po jego zaznaczeniu wpisujemy i potwierdzamy hasło odbezpieczające kod. Potem wystarczy tylko zapisać skoroszyt i już. Po kolejnym otwarciu pliku dostęp do kodu VBA będzie już zablokowany. Chcąc wejść do kodu konieczne będzie wykonanie operacji odwrotnej tzn. wejście do edytora i kliknięcie prawym przyciskiem myszy. Pojawi się formularz z pytaniem o hasło. Po wpisaniu – kod będzie dostępny.
Zabezpieczenie kodu będzie aktywne tak długo, dopóki nie zostanie odhaczony checkox zabezpieczający kod.

Przy okazji, nieco rozszerzając temat zabezpieczenia kodu, warto też wspomnieć o możliwości częściowego kodu z poziomu arkusza. Przy bardziej złożonych projektach, wykorzystujących większą ilość procedur zapisanych w modułach ogólnych, wszystkie o charakterze publicznym są widoczne w oknie makr czy funkcji użytkownika.

Najczęściej większość z nich nie jest tu potrzebna, stanowią tylko podprocedury wywoływane kodem z innych procedur. W takich przypadkach warto ustawić opcję prywatności dla całych modułów.  Wystarczy na górze modułu wpisać Option Private Module.

Wszystkie zapisane w tym module procedury będą niewidoczne w oknie makr/funkcji użytkownika, a jednocześnie będą dostępne z poziomu wszystkich innych modułów skoroszytu.

 

 

Kurs Excel - makra i VBA dla początkujących

 

Ochrona skoroszytu

Ochrona skoroszytu to zabezpieczenie układu skoroszytu i/lub widoków.
Ochronę skoroszytu włączamy na karcie Recenzja:

Po naciśnięciu przycisku pokaże się formularz z miejscem na wpisanie hasła (można pominąć) oraz 2 opcjami blokady:

      • ochrona struktury
        powoduje zablokowanie możliwości dodawania/usuwania arkuszy. Niedostępne jest także ukrywanie i odkrywanie arkuszy.
      • ochrona okna
        niedostępna jest większość opcji Okno na karcie Widok.

Włączona ochrona skoroszytu obejmuje wszystkie znajdujące się w nim arkusze.

 

 

Ochrona arkusza

Ochrona arkusza to zabezpieczenie przed wprowadzaniem zmian poszczególnych elementów arkusza.
Ochronę arkusza włączamy na karcie Recenzja:

Po naciśnięciu przycisku pokaże się formularz, w którym zaznaczamy te opcje, na które chcemy pozwolić użytkownikowi. Jeżeli nie zaznaczymy żadnej z dostępnych opcji, nie będzie można nawet zaznaczyć komórek.

W formularzu tym można wpisać hasło – wówczas do zdjęcia ochrony konieczne będzie jego wpisanie. Jeżeli pole to zostawimy puste – do zdjęcia ochrony wystarczy naciśnięcie przycisku.

Po włączeniu ochrony arkusza (niezależnie od zaznaczenia opcji związanych z formatowaniem czy wstawianiem/usuwaniem), nie można edytować komórek. Jest to dobre rozwiązanie w przypadku komórek zawierających formuły, ale blokada wszystkich komórek uniemożliwia również wpisywanie danych, które powinny być wprowadzone. Oznacza to, że komórki do edycji musimy odblokować.
Zaznaczamy zakres komórek do edycji, przechodzimy do formatowania komórek (korzystając np. z menu podręcznego) i aktywujemy zakładkę Ochrona.

Pole Zablokuj jest domyślnie zaznaczone (dla wszystkich komórek w arkuszu), więc konieczne jest jego odznaczenie. Po zdjęciu blokady – wybrane komórki będą dostępne do edycji.
Jeżeli ochrona arkusza nie jest włączona, blokada komórki jest nieskuteczna.

Warto też pamiętać, że przy włączonej ochronie arkusza nie ma możliwości ukrywania/odkrywania wierszy/kolumn czy innych obiektów.

 

Kurs Excel 2010 esencja

Ochrona pliku Excel

Zabezpieczenie plików Excel przed nieuprawnioną działalnością (odczytem danych czy wprowadzaniem szkodliwych zmian) to bardzo ważny i popularny temat. Wprawdzie dobry haker poradzi sobie i tak, ale często warto zabezpieczać własną pracę także przed zwykłymi użytkownikami, którzy mniej lub bardziej świadomie mogą wprowadzić takie zmiany w skoroszycie, że nic nie będzie działać.

Najpopularniejsze sposoby ochrony pliku Excel to:

Tyle hasłowo, notki opisujące każdy z tych sposobów zabezpieczenia skoroszytu będą się pojawiać sukcesywnie.

 

Kurs Excel 2013 od podstaw

Power Query

Power Query (w skrócie PQ) to dodatek do Excela, który umożliwia pobranie i analizę danych z innych plików(nie tylko Excela, również wielu innych),  stron internetowych itp. Dużym plusem jest to, że dane są wstawiane do tabeli w formie łącza, a więc mogą być automatycznie aktualizowane.

PQ dla wersji Excela 2010 i 2013 trzeba pobrać jako plik ze strony Microsoft Office:
Dodatek Power Query dla programu Excel

Opis instalacji dodatków jest tu:
Dodatki programu Excel

A praktyczne wykorzystanie PQ? To temat rzeka, któremu zamierzam poświęcić wiele notek na tym blogu – zakładam tag Power Query.

Lista rozwijalna

Lista rozwijalna to często stosowany sposób na wymuszenie możliwości wpisywania do komórki tylko konkretnych wartości. Jest też bardzo praktyczna, gdyż wystarczy kliknięcie na jeden z elementów listy, aby wprowadzić ją do komórki.
Efekt ten uzyskamy poprzez zastosowanie opcji Poprawność danych, dostępnej na karcie Dane.

W formularzu, który się otworzy, wybieramy Lista:

Definicję wartości, które mają się pojawić na liście wpisujemy w pole Źródło.
Jest tu kilka sposobów:

    • bezpośredni wpis poszczególnych elementów listy, oddzielonych średnikami. Do zastosowania w przypadkach, gdy wybór ma tylko kilka wartości
    • wpisanie zakresu komórek, w których są wartości mające się pokazać jako elementy listy
      lista rozwijalna
    • odwołanie do zdefiniowanej nazwy zakresu (zakres musi mieć szerokość 1 kolumny, nie może mieć więcej)
    • odwołanie do zakresu dynamicznego (ilość elementów zmienia się w zależności od niepustych komórek w zaznaczonej kolumnie). W polu Źródło wpisujemy funkcję:
      =PRZESUNIĘCIE(Arkusz3!$A$1;;;ILE.NIEPUSTYCH(Arkusz3!$A:$A);1)
      – oczywiście dostosowując nazwę arkusza i kolumnę z danymi do własnych potrzeb.

Nie pamiętam jak to było w Excelu 2007, ale od wersji 2010 źródło listy rozwijalnej może być w innym arkuszu niż ten, w którym znajduje się lista. Przy bardziej rozbudowanych projektach i różnych listach warto umieścić je w osobnym arkuszu. Może być ukryty.