[Excel, VBA] Tabellenblätter sperren/entsperren & Sperrung aufheben
Gepostet am: May 19, 2011 7:2:40 PM
Wer öfters mal Excel-Tabellen mit Kunden tauscht, und Kunden nur bestimmte Zellen zur Bearbeitung zur Verfügung stellen will, kommt um den Blattschutz von Excel nicht herum.
Zellen mit bestimmten Formatierungen von Sperrung ausnehmen
Das Problem dabei ist, dass man Excel vorher mitteilen muss, welche Zellen man nicht geschützt haben will, die also vom Kunden oder Geschäftspartner editiert werden dürfen. Für gewöhnlich haben die nicht zu sperrenden Zellen ähnliche Eigenschaften. Beispielsweise die selbe Formatierung. Ich beispielsweise formatiere die Zellen, in denen eingegeben werden soll immer als Eingabe.
Will man nun alle Tabellenblätter mit allen Zellen, mal abgesehen von den als Eingabe formatierten sperren, könnte man folgenden VBA-Code verwenden.
VBA Blattschutz
Sub Arbeitsmappe_sperren()Dim strPassword As StringDim wks As Worksheet Dim mRange As Range strPassword = InputBox("Passwort eingeben:", "Passwort") Application.ScreenUpdating = FalseFor Each wks In Worksheets For Each mRange In wks.UsedRange If mRange.Style = "Eingabe" Then If InStr(1, mRange.Address, ":") <> 0 Then mRange.Locked = False Else mRange.MergeArea.Locked = False End If Else If InStr(1, mRange.Address, ":") <> 0 Then mRange.Locked = True Else mRange.MergeArea.Locked = True End If End If NextNext'Arbeitsblätter sperrenFor Each wks In Worksheets wks.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True wks.EnableSelection = xlUnlockedCells Next Application.ScreenUpdating = TrueEnd Sub
nur Zellen mit Formeln sperren
Das kann man natürlich auch auf andere Eigenschaften der Zelle ändern. Sinnvoll erscheint es beispielsweise alle Zellen, die Formeln enthalten zu sperren, und alle anderen nicht.
nur Zellen mit Formeln sperren
Sub Arbeitsmappe_sperren()Dim strPassword As StringDim wks As Worksheet Dim mRange As Range strPassword = InputBox("Passwort eingeben:", "Passwort") Application.ScreenUpdating = FalseFor Each wks In Worksheets If wks.ProtectContents = False Then For Each mRange In wks.UsedRange If Left(mRange.Formula, 1) <> "=" Then If InStr(1, mRange.Address, ":") <> 0 Then mRange.Locked = False Else mRange.MergeArea.Locked = False End If Else If InStr(1, mRange.Address, ":") <> 0 Then mRange.Locked = True Else mRange.MergeArea.Locked = True End If End If Next End IfNext'Arbeitsblätter sperrenFor Each wks In Worksheets wks.Protect Password:=strPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True wks.EnableSelection = xlUnlockedCells Next Application.ScreenUpdating = TrueEnd Sub
Kommentare trotzdem zulassen
Häufig ist es sinnvoll, den Geschäftspartnern wenigstens die Kommentarfunktion für die entsprechenden Zellen zu erlauben. Dafür ändert man die Protect-Anweisung folgendermaßen:
Kommentare zulassen
wks.Protect Password:=strPassword, DrawingObjects:=False, Contents:=True, Scenarios:=True wks.EnableSelection = xlNoRestrictions
Sicherheit des Passwortschutzes
Der Blattschutz ist keine sonderlich sichere Sache, da er nur den Zweck verfolgt, versehentliches Überschreiben zu verhindern. Das macht er aber sehr zuverlässig. Ein kleines Tool, um den Blattschutz zu entfernen stelle ich hier zur Verfügung.