Dostęp do danych w Arkuszu.

Jak większość wie do danych w arkuszach możemy ograniczyć dostęp zmieniając właściwości komórek i włączając ochronę arkusza.

Każda komórka arkusza ma możliwość ustawienia dwóch istotnych parametrów w zakładce “ochrona”. Jest tam opcja zablokuj i ukryj.

Opcja zablokuj przeznaczone jest do blokowania możliwości edycji danych zawartych w komórce.

Natomiast opcja ukryj sprawia, że zaznaczając taką komórkę w wierszu formuły nie widzimy jej zawartości – trudniej jest określić czy dana tam zawarta, to wartość, czy wynik obliczeń formuły. Dodatkowo opcja ukryj sprawia, że w przypadku niezaznaczonej opcji zablokuj wchodząc w tryb edycji komórki pole staje się puste – znika wcześniej widoczna tam zawartość.

Ustawienie powyższych opcji nic nie wnosi w działanie arkusza aż do momentu, kiedy włączymy jego ochronę. Włączenie ochrony wywołuje następujące skutki:

  • komórki z włączoną opcją zablokuj nie mogą być edytowane,
  • komórki z włączoną opcją ukryj nie wyświetlają w pasku adresu swojej zawartości po ich zaznaczeniu a w trybie edycji “znika” ich zawartość,
  • komórka z włączonymi oboma właściwościami nie wyświetla po zaznaczeniu w wierszu formuły swojej zawartości.

Powiązanie opcji zablokuj i ukryj często jest stosowane (w zależności od konfiguracji) do zabezpieczenia formuł przed niepowołaną edycją a czasem również do ukrycia treści formuły.

Arkusz w trybie chronionym i niechronionym to często stosowane i ogólnie znane tryby jego pracy, ale nie jedyne do wykorzystania! Podczas włączania ochrony arkusza możemy przed dodatkowe opcje zmodyfikować zakres uprawnień użytkownika, ale o tym kiedy indziej….

Jest jeszcze trzeci mniej znany, ale bardzo przydatny tryb pracy arkusza szczególnie tym, którzy wykorzystują makra do obsługi arkuszy, czy automatyzacji powtarzalnych czynności.

Arkusz można ustawić w tryb InterfaceOnly. W tym trybie z poziomy makra możemy zmieniać zawartość komórek, ale użytkownik już nie, chyba, że ma odblokowany dostęp do zakresów, czy wybranych komórek.

Niestety ten tryb pracy przynajmniej ja umiem ustalić tylko z poziomu makra.

Typowy kod generowany przez rejestrator makr przy blokowaniu arkusza wygląda następująco:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions

Natomiast jeśli chcemy arkusz ustawić w tryb InterfaceOnly należy ten kod zmodyfikować do postaci:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterFaceOnly:=InterfaceOnly
ActiveSheet.EnableSelection = xlNoRestrictions

W tak zablokowanym arkuszu możemy z poziomu makra zmieniać jego zawartość, ale niestety są ograniczenia dla części funkcjonalności dostępnych z poziomu makr.

Na forach trafiłem na takie operacje niedostępne w trybie interfaceonly jak:

  • PivotCache.Refresh,
  • AddComment,
  • FormatConditions,
  • Validation.Add

Możliwe, że jest więcej, ale ja na razie nie trafiłem. Sam zmagałem się z 2 i czwartym ograniczeniem. Skończyło zdjęciem ochrony na czas wykonywania kodu tego przez makro i potem ponownego zabezpieczenia.

Niestety o ile tryb chroniony i niechroniony ustalamy na stałe o tyle tryb jest interfaceonly “ulotny” i przy ponownym otwarciu arkusza trzeba go ponownie ustawiać, choć osobiście uważam, że “gra warta zachodu”, bo kodu jest mniej.

Na razie tyle. Może w wolnej chwili uda mi się to uzupełnić o więcej kodu.