Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 26933 Skopiuj do schowka 43. Zmiana w arkuszu chronionym (VBA)
Autor Wiadomość
Artik 



Wersja: Win Office 365
Posty: 8611
Wysłany: 01-01-2012, 20:25   43. Zmiana w arkuszu chronionym (VBA)

Dość często pojawiającym się na forum zapytaniem jest, co zrobić by dokonać kodem zmian w arkuszu chronionym?
Ciśnie się na usta proste rozwiązanie - zdjąć ochronę, zmienić co trzeba i założyć ochronę ponownie. Można. Proste i logiczne.
Ponieważ lubię mieszać (bo bez mieszania, to ciasto raczej nie wyjdzie ;-) ), więc i czytelnikowi tego wątku spróbuję namieszać nieco w głowie. :-)

Można działać jak logika nakazuje. Ale można też wykorzystać stosunkowo mało znaną metodę ochrony w specjalnym trybie - programiści Excela przewidzieli, więc może warto się temu przyjrzeć.

Z punktu widzenia przeciętnego użytkownika arkusza rozróżnia on dwa stany arkusza jeśli chodzi o ochronę:
1. Albo arkusz nie jest chroniony,
2. albo arkusz jest chroniony.

Programiści mają dostęp do jeszcze jednego stanu:
3. Arkusz jest chroniony w specjalnym trybie.

Ten tryb jest osiągalny tylko z poziomu kodu. Nie da się go włączyć ręcznie za pomocą dostępnych w aplikacji ustawień.

Wspomniany tryb ma swoje "zady i walety". Zaletą jest, że nie trzeba ciągle odbezpieczać i zabezpieczać arkusza gdy chcemy w nim coś zmienić. Wadą - jest to tryb ulotny. Arkusz jest chroniony specjalnie tylko do czasu zamknięcia skoroszytu. Po ponownym otwarciu, ochrona arkusza zmienia się na "zwykłą".

Rozważmy taką budowę i działanie skoroszytu - zawiera on kilka arkuszy, które mają być chronione przed bezpośrednią ingerencją użytkownika w ich treść. Ponieważ kodem wielokrotnie będziemy zmuszeni ingerować w treść arkuszy nie będziemy zadowoleni, że ciągle, w każdej procedurze, musimy odbezpieczać arkusz, dokonywać istotnych zmian i zabezpieczać go ponownie.
Na ratunek możemy wezwać ochronę w trybie specjalnym.
1. Przy otwarciu skoroszytu zabezpieczamy specjalnie wszystkie arkusze.
2. Gdy istnieje potrzeba zmian w arkuszach uruchamiamy kod, który poprostu ingeruje w treść arkuszy nie zważając na to, czy arkusze są chronione czy nie.
Prawda, że brzmi to zachęcająco?

Dobra. My tu pierdu pierdu, gadu gadu, ale jak to zrobić?
Normalna ochrona aktywnego arkusza kodem za pomocą makra wygląda mniej więcej tak:
Kod:
  ActiveSheet.Protect Password:="Moje_hasło", _
                      DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True
Wszystkie parametry są opcjonalne.

Ochrona w trybie specjalnym wygląda tak:
Kod:
  ActiveSheet.Protect Password:="Moje_hasło", _
                      DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True, _
                      UserInterfaceOnly:=True
Jak widać, żadna filozofia. Po prostu dodany jest nowy parametr - UserInterfaceOnly z wartością True.

Wcześniej wspomniałem, że tryb specjalny jest ulotnym. Jak więc z niego korzystać na co dzień?

Przy założeniu, że interesuje nas ochrona wszystkich arkuszy skoroszytu, najczęściej przy otwarciu skoroszytu włączymy tryb specjalny. W module ThisWorkbook(w XL2K10 - Ten_Skoroszyt) wstawiamy procedurę zdarzeniową:
Kod:
Private Sub Workbook_Open()
  Dim wks      As Worksheet

  For Each wks In ThisWorkbook.Worksheets
    wks.Protect Password:="Moje_hasło", _
                DrawingObjects:=True, _
                Contents:=True, _
                Scenarios:=True, _
                UserInterfaceOnly:=True
  Next wks
End Sub
I odtąd - bajka.
Zamiast takiego kodu, który zmienia wartość w komórce A1 aktywnego i chronionego arkusza:
Kod:
Sub AAA()
  ActiveSheet.Unprotect Password:="Moje_hasło"
 
  Range("A1").Value = "Teraz jest: " & Now
 
  ActiveSheet.Protect Password:="Moje_hasło", _
                      DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True
End Sub
możemy napisać taki:
Kod:
Sub BBB()
 
  Range("A1").Value = "Teraz jest: " & Now
 
End Sub
Jeżeli wiemy, że przy otwarciu skoroszytu włączyliśmy ochronę w trybie specjalnym, nie musimy się prawie wcale przejmować w innych procedurach czy arkusz w ogóle jest, czy nie jest chroniony.

Jak widać proste dodanie parametru UserInterfaceOnly i ustawienie go na True może nam znacznie ułatwić pisanie procedur.

Z trybem specjalnym ochrony wiąże się jeszcze sprawdzanie, czy arkusz jest chroniony w tym trybie. Właściwość ProtectionMode odpowie nam na to pytanie. Zwraca True, gdy arkusz jest chroniony specjalnie oraz False dla każdego innego stanu ochrony (brak ochrony lub ochrona zwykła). Gdyby zależałoby nam na tym, by arkusz był zawsze chroniony (specjalnie) bez względu na to czy użytkownik mógł zdjąć ochronę ręcznie czy jest chroniony "normalnie", w każdej z istotnych procedur należy dodać warunek sprawdzający w jakim stanie ochrony jest arkusz. Jeżeli niechroniony lub chroniony normalnie, to wymuśmy ochronę specjalną:
Kod:
Sub CCC()

  If Not ActiveSheet.ProtectionMode Then
    ActiveSheet.Protect Password:="Moje_hasło", _
                        DrawingObjects:=True, _
                        Contents:=True, _
                        Scenarios:=True, _
                        UserInterfaceOnly:=True
  End If

  Range("A1").Value = "Teraz jest: " & Now

End Sub


Użycie kodem specjalnego trybu chronionego ma jeszcze jedną ciekawą właściwość. Mianowicie, odbezpieczenie arkusza ręcznie przez użytkownika, a następnie ponowne jego zabezpieczenie nie niszczy trybu specjalnego. Podobnie w działaniu kodem. Odbezpieczenie
Kod:
Activesheet.Unprotect Password:="Moje_hasło"
i zabezpieczenie:
Kod:
  ActiveSheet.Protect Password:="Moje_hasło", _
                      DrawingObjects:=True, _
                      Contents:=True, _
                      Scenarios:=True
Prosze zwrócić uwagę, że nie zastosowano parametru UseInterfaceOnly. Ale mimo tego arkusz jest nadal chroniony w trybie specjalnym.

Tyle na DZIŚ(). ;-)

Artik
ID posta: 142626 Skopiuj do schowka
 
 
master_mix 
Excel Expert



Posty: 2098
Wysłany: 01-01-2012, 23:49   

Bardzo ładnie Art to opisał.

Ja od siebie dorzucę:

Często stosuję tą "specjalną" ochronę, zauważyłem jednak że w niektórych przypadkach pomimo UserInterfaceOnly (czyli pozwolenia na zmiany w arkuszu kodem) niektórych rzeczy nie da się wykonać bez zastosowania Unprotect, Protect.
W tej chwili dokładnie nie pamiętam wszystkich wyjątków, bo na pewno było ich więcej niż jeden, ale na pewno:
Validation.Add - > bez Unprotect nie bangla

Edit Artik:
Ha! zabezpieczyłem się na tą ewentualność pisząc:
Cytat:
...nie musimy się prawie wcale przejmować...
Potwierdzam, że w szczególnych przypadkach trzeba niestety wahlować zdejmowaniem i zakładaniem ochrony.

Ale to jest artykuł dla początkujących. :-)
_________________
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Podejmę współpracę (pracę) w zakresie tworzenia aplikacji arkusza kalkulacyjnego z wykorzystaniem VBA. Programowanie VB.NET, ASP.NET, WPF. Technologie LINQ, Entity Framework. Aplikacje klienckie dla baz danych SQL Server, Oracle, MySQL
Wrocław i okolice …lub zdalnie.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ID posta: 142635 Skopiuj do schowka
 
 
Wormsek 



Zaproszone osoby: 2
Wersja: Win Office 2016
Posty: 5267
Wysłany: 02-01-2012, 08:33   

Fajne zebranie wszystkiego do kupy, bo pamiętam, że kiedyś zażarcie z tym walczyłem ;-) .

"Lubię to" ;-) :mrgreen:
_________________
Pozdro
Worm

FAQ - Najczęściej zadawane pytania.
JAK KORZYSTAĆ Z SZUKAJKI
Słownik funkcji

Znajdź nas na Facebook'u

A może fajny dodatek do excela?
ID posta: 142639 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Posty: 6875
Wysłany: 02-04-2013, 10:01   

Do wyjątku, który podał master_mix dorzucę jeszcze:
.PivotCache.Refresh
.AddComment
.FormatConditions
_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 191293 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe