Jste zde: Úvodní stránka » excel » vba-listy-sheet » Zamykani-odemykani-listu-Excel-VBA

Zamykání a odemykání listu Excel VBA

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Jak na zamykání a odemykání listů. aneb "bezpečnost" a co vše se dá nastavit před a při zamykáním listů.

Úvodem do zamykání a odemykání listů

Mnohdy je potřeba ochránit listy, nejen před ostatními, ale i před sebou samým, ať si člověk nepřepíše vlastní vzorečky, funkce atd. Nejen tedy jak zamknout list (co vše se dá kromě hesla při zamykání nastavit), ale i jak povolit zápis do určitých buněk, skrýt vzorečky (i když tohle si myslím, že je zbytečné, mnohdy stačí "jen" skrýt list). Opět z důvodu přehlednosti jsme článek rozdělil

zamknout/odemknout list - základ
zamknout/odemknout list s heslem
zamknout/odemknout - více listů - dotaz na heslo
zamknout/odemknout - dotaz na heslo
stav zamknuto/odemknuto
povolování zápisu
skrývání vzorců
atributy Protect - více o metodě
další články
ke stažení

zamknout/odemknout list - základ

Úplný základ jak zamknout nebo odemknout list v Excel. At už přes název listu. nebo přes kódové jméno listu. Více o označování listů v samostatném článku.


' Zamknout list, se názvem List1
Sheets("List1").Protect

' Odemknout list
Sheets("Sheet1").Unprotect

' vyuzitim kodoveho nazvu listu
ListZamek.Protect
ListZamek.Unprotect

zamknout/odemknout list s heslem

Pokud budete chtít zamykat list s heslem. Osobně zamykám bez hesla (zabezpečení Excel moc nevěřím), ale budete-li chtít, můžete


' vyuzitim jména listu a heslem Heslo
Worksheets("List1").Protect Password:="Heslo" 
Worksheets("List1").Unprotect Password:="Heslo" 

' vyuzitim kodoveho nazvu listu
ListZamek.Protect Password:="Heslo" 
ListZamek.Unprotect Password:="Heslo"

zamknout/odemknout - více listů - dotaz na heslo

Zamknout všechny listy


    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect ' Unprotect pro odemceni
    Next ws

Zamknout všechny listy při otevření sešitu s heslem


Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="Heslo"
	' Unprotect Password:="Heslo" pro odemceni
    Next ws
End Sub
' události sešitu jsou samsotatna kapitola

dotaz na heslo

Jak ošetřit chybně zadané heslo. Zde bez dynamického názvu. Špatné heslo zadáno ručně v kódu. Kód skončí chybou


' Skončí chybou pokud je špatné heslo je potřeba ošetřit
Sheets("zamek").Unprotect Password:="ChybneHeslo"

Ošetření chybně zadaného hesla


On Error Resume Next
Sheets("zamek").Unprotect Password:="ChybneHeslo"

If Err.Number <> 0 Then
    MsgBox "Chybne heslo."
    Exit Sub
End If

On Error GoTo 0

Dotaz na heslo a ošetření chybně zadaného hesla


Dim Heslo As Variant
Heslo = InputBox("Zadej heslo")

On Error Resume Next
Sheets("zamek").Unprotect Password:=Heslo

If Err.Number <> 0 Then
    MsgBox "Chybne heslo."
    Exit Sub
End If

On Error GoTo 0
' tip - excel se dá uzavřít

stav zamknuto/odemknuto

Někdy se hodí vědět, zda je list zamknut či zda má chráněné objekty nebo scenáře.


MsgBox StavZmknuti = Sheets("zamek").ProtectContents
' Kontrola zda je list zamceny

If Sheets("zamek").ProtectContents Then 
MsgBox "Zamčeno."
end if

'Check if worksheet objects are protected
If Sheets("zamek").ProtectDrawingObjects Then MsgBox "chraněny objekty"

'Check if worksheet scenarios are protected
If Sheets("zamek").ProtectScenarios Then MsgBox "chráněny scénaře"

Poznámka pro strýčka příhodu. Budu-li potřebovat rozepíši se více.

povolování zápisu

Potřebuji-li povolit zápis do určitých buněk, při zamčeném sešitě.


Range("F17:F18").Locked = False         ' True

Vracení do defaultního režimu, nebo-li po zamknutí listu, se nebude moci do buněk dát zapisovat


Range("F17:F18").Locked = True
' odkazy na sešit, list, využití proměnné atd. 
' viz jiné ukázkové příklady

skrývání vzorců

Osobně vzorce neskrývám. pro skrytí vzorce, po zamčení sešitu nepůjdou vidět v řádcích vzorců.


Range("F17:F18").FormulaHidden = False  ' True

Zobrazení vzorců v dané buňce, oblasti.


Range("F17:F18").FormulaHidden = True

Atributy metody Protect

Tato kapitola je v přípravě.


Sheets("zamek").Protect Password:="Heslo", _
  DrawingObjects:=False, _
  Contents:=True, _
  Scenarios:=False, _ 
  UserInterfaceOnly:=True, _ 
  AllowFormattingCells:=True, _
  AllowFormattingColumns:=True, _
  AllowFormattingRows:=True, _
  AllowInsertingColumns:=False, _
  AllowInsertingRows:=False, _
  AllowInsertingHyperlinks:=False, _
  AllowDeletingColumns:=True, _
  AllowDeletingRows:=True, _
  AllowSorting:=False, _
  AllowFiltering:=False, _
  AllowUsingPivotTables:=False

Popis argumentů

v přípravě

Atribut	Doporučeno	Data type	Default	Popis
Password	Optional	Variant	---	možnost zadat heslo
DrawingObjects	Optional	Variant	True
Contents	Optional	Variant	True
Scenarios	Optional	Variant	True
UserInterfaceOnly	Optional	Variant	True
AllowFormattingCells	Optional	Variant	False
AllowFormattingColumns	Optional	Variant	False
AllowFormattingRows	Optional	Variant	False
AllowInsertingColumns	Optional	Variant	False
AllowInsertingRows	Optional	Variant	False
AllowInsertingHyperlinks	Optional	Variant	False
AllowDeletingColumns	Optional	Variant	False
AllowDeletingRows	Optional	Variant	False
AllowSorting	Optional	Variant	False
AllowFiltering	Optional	Variant	False
AllowUsingPivotTables	Optional	Variant	False

Praktické využití argumentů

Kódy v přípravě.


Sheets("zamek2").Protect Password:="HesloMoje", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False

Další články

Chcete se více dozvědět co zle provádět s listem nebo listy

Událostí listu - Excel VBA
Práce s listy sheet - Excel VBA - výběr, přidání, přejmenování, smazání ...
Duplicitní hodnoty - odebrat - Excel VBA
....

Ke stažení

Soubor ke stažení zdarma je v přípravě. Soubor využívá makra.

Závěrem

Chybí vám něco v tomto článku co se týká bezpečností v listu/listech, můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:07

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.