ID tematu: 26933
 |
43. Zmiana w arkuszu chronionym (VBA) |
Autor |
Wiadomość |
Artik
Artik


Wersja: Win Office 365
Posty: 10781
|
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
|
|
|
 |
|
|
|
master_mix
Excel Expert


Wersja: Win Office 365
Posty: 2637
|
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ę)
Programowanie C#, Android, iOS, VB.NET, VBA, ASP.NET Core, WPF, Xamarin, Power Platforms, XAML, MVC, LINQ, Entity Framework. Bazy danych SQL Server, Oracle, MySQL, Firebird
Wrocław i okolice …lub zdalnie. |
|
 | ID posta:
142635
|
|
|
 |
|
|
Wormsek


Zaproszone osoby: 2
Wersja: Win Office 2016
Posty: 5295
|
|
 | ID posta:
142639
|
|
|
 |
|
|
Marecki
Excel Expert


Wersja: Win Office 2021
Posty: 8821
|
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ć.
FB |
|
 | ID posta:
191293
|
|
|
 |
|
|
Artik
Artik


Wersja: Win Office 365
Posty: 10781
|
Wysłany: 15-08-2021, 09:48
|
|
|
Kolejny "kfiatek".
Zmiana rozmiaru tabeli:
.ListObjects(1).Resize ...
Artik |
_________________ Persistence is a virtue in the world of programming.
Weryfikator NIP - szybka, masowa weryfikacja w MF i VIES. |
|
 | ID posta:
408279
|
|
|
 |
|
|
Artik
Artik


Wersja: Win Office 365
Posty: 10781
|
Wysłany: 14-10-2021, 12:33
|
|
|
sanctus wrzucił kolejny kamyczek do ogródka: Cytat: | Funkcja .ApplyChartTemplate dla wykresów nie działa. |
Artik |
_________________ Persistence is a virtue in the world of programming.
Weryfikator NIP - szybka, masowa weryfikacja w MF i VIES. |
|
 | ID posta:
410047
|
|
|
 |
|
|
|
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
|
|
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
|