Zamykání/odemykání buněk - Protect/Unprotect - 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 pomocí VBA Excel zamykat (Protect) a odemykat (Unprotect) buňky.
Úvodem o zamykání buněk pomocí VBA v Excelu
Ochrana (Protection) buněk je vhodná, nemusí jít ani o požadavek, aby Vám někdo neodhalil Vaše funkce a vzorce, ale pouze o to ať si své vzroce nepřepíšete. Elegantně jde toto vyřešit pomocí zamykání. Jak na zamykání ve VBA je sepsáno v tomto článku.
Upozornění: Heslo v Excel není úplně bezpečné, stačí mít trochu znalosti (případně si stáhnout příslušný software) a heslo překonate. Proto berte tuto ochranu jako základní, aby pro nepřepsání si důležitých údajů. Uchovávat v tom finanční přehledy firmy zabezpečené pouze heslem nedoporučuji. Raději tento soubor zašifrujte profsionálním nástrojem. A hlavně když už používate heslo tak jej nezapomenout...
Odemknout - změnit - zamknout
Nezapomenout: V 19. století se zamykalo! - Hláška z návštěvníku.
Nejčastější a nejednoduší úloha. Sešit je zamknut a mi pomocí VBA potřebujeme změnit zamknoutou buňku. Jak na to? Odemknout, změnit a nezapomenout zamknout.
Odemknout
Předpokládám, že heslo znáte. Pokud hledáte jak obejít heslo musíte se zeptat strýčka googla.
ThisWorkbook.Worksheets("List1").Unprotect Password:="Heslo"
Změnit
Něco změníme. Třeba v buňce A1.
ThisWorkbook.ActiveSheet.Range("A1").FormulaR1C1 = "Provedena změna :)"
Zamknout
Tak a list opět ochraníme.
ThisWorkbook.Worksheets("List").Protect Password:="Heslo"
Je list chráněn?
Zjištění, zda je list je chráněn.
If ActiveSheet.ProtectContents = True Then
MsgBox "List chráněn. :("
Else
MsgBox "List nechráněn. :)"
End If
Závěrem
Bude-li zájem o další VBA kódy týkající se zamykaní buněk, pokusím se je zodpovědět v komentářích či doplnit tento článek. Zajímavá témata jsou:
- možnosti ochran listu - Excel nabízí mnoho nastavení - jak je aplikovat
- aktuální stav ochrany listu
- nastavení ochrany listu
- změna ochrany listu
- buňky bez ochrany - nastavení oblastí
- atd. - jen z tématu okolo ochrany listu, by se dala napsat diplomka :)
Článek byl aktualizován: 19.09.2020 11:06
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ů.
Komentáře
kosokrychlov
Přidáno: 12.12.13 15:14
Zdravim, prosím o radu. Zamčený list - při spuštění makra na začátku odemkne, provede se zadaný kod, list zase zamkne. Funguje správně, ale při sdílení sešitu není možné odemknout/zamknout list. Existuje na to nějaké řešení? Bylo by například možné při sdílení sešit nechat zamčený a nastavit "Povolit uživatelům úpravy oblastí" s heslem? Pokud ano jak by se to zapisovalo ve VBA. Díky za odpověď
Tomáš
Přidáno: 16.06.14 12:50
Zdravím, mám dotaz k zamykání. Mám makro, kde jeden z produktů jsou grafy, po proběhnutí makra všechno zamknu, a tady vznikne problém - graf nelze označit, aby šel samostatně vytisknout. A naopak, když jej nechám odemčený, tak pak je možné, aby ho uživatel smazal, a tomu chci zabránit. Napadá Tě nějaké řešení?
Ivana
Přidáno: 10.07.14 15:35
Dobrý den, Bojuji se zamykáním buněk. Správně vyberu ty, které nechci zamknout a pak celý list zamknu. Vše funguje OK do doby, než potřebuji použít filtry na daném listě. Umíte mi poradit? Díky
Stoupa
Přidáno: 22.08.14 06:37
Zdravím vespolek,
řeším něco podobného a prozatím zcela bez úspěchu :(
mám xls soubor, v něm 30 listů, spousta vzorečků, takže je nasnadě je uzamknout. Občas ale doplňuji data, takže jsem potřeboval vytvořit ve VBA, hromadné uzamčení/oemknutí listů.
S tím jsem si poradil (formou tlačítka v listu, nebo vyvoláním pole za pomoci klávesové zkratky).
Mám ale "těžký" :) problém:
potřebuji zpřístupnit komentáře (klasickým uzamčením se jednoduše zaškrtne volba "Upravit Objekty"), ale ve VBA vůbec nemůžu narazit na ten správný parametr (DrawingObjects:=True, AllowFormattingRows:=True, etc...) Lidé dobří, věděl by někdo ... ?
S tím souvisí možná už "vyšší dívčí" - pokud se totiž povolí objekty, tak lze manipulovat s vytvořenými tlačítky -> karta "Vývojář"-"Režim návrhu" :( Lze toto nějak obejít ?
A nakonec něco pro Ivanu,
ale nevím, jestli jsem přesně pochopil problém.
Po uzamčení není filtr funkční ?
No v rychlosti jsem zkusil pro tlačítko něco spáchat (odemyká/zamyká všechny listy najednou):
za hodnotou "list.Protect Password:=heslo" jsou všechny mě známé parametry, jen právě ty objekty nevím :(
Private Sub CommandButton1_Click()
Dim list As Worksheet
heslo = InputBox("Zadej heslo", "ZÁMEK")
For Each list In Worksheets
If list.ProtectContents = True Then
list.Unprotect Password:=heslo
Else
list.Protect Password:=heslo, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End If
Next list
End Sub
Stoupa
Přidáno: 22.08.14 12:43
Dodatek k předchozímu ...
takže pro povolení filtrů je ten správný parametr:
AllowFiltering:=True
pro úplnost ;) kód pro klávesové zkratky
Private Sub CommandButton2_Click()
Dim list As Worksheet
For Each list In Worksheets
If list.ProtectContents = True Then
list.Unprotect Password:=TextBox1.Text
Else
list.Protect Password:=TextBox1.Text, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End If
Next list
Unload Me
End Sub
vybrat Insert>UserForm
- zobrazí se Toolbox
vybrat View>Toobox
- zadat Label, CommandButton, TextBox
viditelný popis (název) prvků je v Properties vždy kolonka Caption
- v Properties pro TextBox najít pole PasswordChar a zadat * (jinak se heslo bude zadávat viditelně)
vybrat Insert>Module a zadat:
Sub ShowPass()
UserForm1.Show
End Sub
vybrat Vývojář-Makra (nebo ALT+F8) a zvolit si vyvolávací zkratku (používám Ctrl+Shift+M)
výsledek stejný jako u tlačítka
Bara
Přidáno: 04.06.15 14:36
dobrý den, potřebuji poradit. mam v tabulku se vzorci. Mam okruh lidi, kteri maji pouze nahled.(zamknu list), ale mam i okruh lidi kteri potrebuji do urcitych casti zapisovat. V pripade ze jim odemknu list (musi byt zamcen kvuli prvnimu okruhu lidi)tak se mi zaroven odemknout i me bunky a casto se stava ze mi prepisi co nemaji. Takze zkracene potrebuji v prvnim okruhu zamknout cely list a po odemceni potrebuji mit jeste zamcene urcite bunky. děkuji za pomoc
Klára
Přidáno: 15.12.16 13:03
Dobrý den, ráda bych se zeptala, zda lze nějakým způsobem zamknout pouze formátování buněk - aby uživatelé excelu mohli pouze vkládat hodnoty a formát buněk se přitom nezměnil (i když budou vkládat klasicky přes ctrl + v a ne přes Vložit pouze hodnoty). Děkuji
Peter Káčer
Přidáno: 19.12.16 18:43
Dobrý deň, viete mi poradit? mam tabulku kde zapisujem namerané hodnoty, po zapísaní ich tlačidlom premiestnim do Databanky ( iný list) kde mam archív nameranych hodnôt. Tento list by som chcel uzamknúť aby nemohol nikto meniť údaje. Problem je keď ho zamknem, nefunuguje mi prvý krok - premiestnovanie údajov. Dík za help. :)
Peter Káčer
Přidáno: 19.12.16 18:47
Ďakujem, už to mám. Pomohol mi Váš web.
Peter Káčer
Přidáno: 19.12.16 20:33
Dobrý deň, mam na zaciatku makro odomknutie listu a na konci zamknutie listu. ale stane sa že necha list odomknuty. čím to je? díky Sub NoveMeranie() ThisWorkbook.Worksheets("DB_201960").Unprotect Password:="201960" Dim r As Long, z As Range Set z = Worksheets("201960").Range("A6:J15") With Worksheets("DB_201960") r = .Cells(.Rows.Count, 2).End(xlUp).Row + 1 .Cells(r, 2).Resize(10, 10).Value = z.Value .Cells(r, 1).Resize(10).Merge: .Cells(r, 1).Value = Date With .Cells(r, 1).MergeArea.Resize(, 11) .Borders(xlEdgeBottom).Weight = xlMedium: .Borders(xlEdgeTop).Weight = xlMedium .Borders(xlEdgeRight).Weight = xlMedium: .Borders(xlEdgeLeft).Weight = xlMedium End With i = MsgBox("Sú údaje v tolerancii?", vbYesNo, "Plat") Select Case i Case vbNo i = MsgBox("Zastaviť produkciu ! ", 16) i = MsgBox("Informovať QM !", 48) Case vbYes MsgBox ("Všetko OK") ThisWorkbook.Worksheets("DB_201960").Protect Password:="201960" End Select End With z.Offset(, 1).Resize(, 9).ClearContents End Sub
Excel
- Videokurzy
- Pdf materiály
- Školení konzultace
- Základy MS Excel
- Soubor - karta
- Domů - karta
- Formát dat
- Vložit
- Grafy - typy, tvorba
- Grafy - úpravy, změny
- Vzorce (Funkce)
- Funkce logické
- Funkce textové
- Funkce vyhledávací
- Funkce statistické
- Funkce matematické
- Funkce datum a čas
- Funkce databáze
- Funkce informace
- Funkce konstrukce
- Funkce nové
- Matice
- Data - karta
- Revize - karta
- Vývojář - karta
- Kontingenční tabulka
- Dashboardy
- Power Query editor
- Power Pivot - View
- VBA úvod a seznamy
- VBA Teorie základy
- VBA Buňky cell
- JAK NA BUŇKY - VBA
- » Vybírat - označovat buňky
- » Range objekt - oblast buněk
- » Resize Offset - změna oblasti
- » Tabulka jako tabulka s VBA
- » CurrentRegion a UsedRange
- » Union-Intersect
- » Přečti údaj z buňky
- » Zapiš údaj do buňky
- » Zapiš vzorec (funkci)
- » Výsledek funkce do proměnné
- » Definovaný název
- Formátování buněk
- » Barvy - Color
- » Barvy - HEX, RGB, číslo
- » Písmo formát (Font)
- » Datum a čas - formát
- » Ohraničení buněk - Borders
- » Komentáře do buněk
- » Zamykání/odemykání buněk
- » Protect/Unprotect cells
- » Prázdné buňky - zjistit
- » Řádky sloupce - základ
- » Řádky a sloupce - kódy VBA
- » Buňky, oblasti - kódy VBA
- Find hledej VBA
- VBA Listy sheet
- VBA vzorce funkce
- VBA Práce se soubory
- VBA Formuláře (UserForm)
- VBA Grafy
- VBA Kontingenční tabulky
- VBA Application
- VBA Pás karet, Menu
- VBA Triky
- VBA praktické příklady kódu
- VBA tahák (cheat sheet)
- Praktické příklady
- Marketing controling finance
- Finanční poradce
- Šablony - Templates
- Testy
- Ostatní
- Obecně o Excelu
- Power BI
- Funkce Google tabulky
- Articles in English
- Office Script
- PowerApps
- Python
- Jiní autoři
Sdílejte
|
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
|
LinkedIn... |
