VBA – Schützen / Aufheben des Schutzes von Arbeitsblättern

In diesem Tutorial erfahren Sie alles über den Schutz von Excel-Arbeitsblättern in VBA, d. h. wie man Arbeitsblätter in Excel schützt oder den Schutz aufhebt.

Schutz von Excel-Arbeitsblättern ohne Kennwort aufheben

Um den Schutz eines Arbeitsblatts aufzuheben, das nicht durch ein Kennwort geschützt ist, verwenden Sie diese einfache Codezeile:

Worksheets("Sheet1").Unprotect

Schutz von Excel-Arbeitsblättern mit Kennwort aufheben

Um den Schutz eines Arbeitsblatts aufzuheben, das mit einem Kennwort geschützt ist, müssen Sie auch das Kennwort eingeben:

Worksheets("Sheet1").Unprotect "Kennwort"

Blattschutz aufheben – Kennwort verloren

Um den Schutz eines Arbeitsblatts aufzuheben, ohne das Kennwort zu kennen, müssen Sie ein Add-in zur Kennwortwiederherstellung verwenden.

Schutz von Arbeitsblättern

Mit dem Arbeitsblattschutz können Sie bestimmte Aspekte des Blattes für die Bearbeitung sperren.

Dieses Menü finden Sie unter Startseite > Format > Blatt schützen oder durch Rechtsklick auf den Namen der Blattregisterkarte:

Meistens wird dies verwendet, um „gesperrte“ Zellen vor der Bearbeitung zu schützen, so dass der Endbenutzer nur bestimmte Zellen bearbeiten kann.

Sie können Zellen sperren, indem Sie sie auswählen und die Registerkarte „Schutz“ des Menüs „Zellenformatierung“ öffnen (STRG + 1).

Sie können den Benutzer auch daran hindern, die Struktur des Arbeitsblatts zu ändern (Einfügen, Löschen oder Ändern der Größe von Zeilen und Spalten) oder mit den Autofiltern zu interagieren, und vieles mehr.

Arbeitsblatt schützen – ohne Kennwort

Vielleicht möchten Sie ein Arbeitsblatt schützen, ohne ein Kennwort einzugeben. Dadurch werden versehentliche Änderungen am Arbeitsblatt verhindert, während der Benutzer auf Wunsch Änderungen vornehmen kann.

Worksheets("Sheet1").Protect

Arbeitsblatt schützen – Kennwortschutz

Worksheets("Sheet1").Protect "Kennwort"

Arbeitsblatteinstellungen schützen

Die obigen Beispiele schützen die Arbeitsblätter mit den Standard-Schutzeinstellungen. Stattdessen möchten Sie möglicherweise anpassen, was geschützt werden soll:

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

Anstatt die obige Syntax zu verwenden, empfehle ich, ein Makro mit den gewünschten Einstellungen aufzuzeichnen (ausgewählt mit dem Menü Arbeitsblattschutz oben) und den aufgezeichneten Code zu kopieren und in Ihre Prozedur einzufügen.

Blatt schützen – Änderungen durch VBA zulassen

Wenn Sie ein Arbeitsblatt schützen, gilt der Schutz standardmäßig nicht nur für Benutzeraktionen, sondern auch für VBA-Operationen. Wenn VBA versucht, eine gesperrte Zelle zu ändern, wird ein Laufzeitfehler 1004 angezeigt. Um dies zu vermeiden, können Sie den Schutz Ihrer Arbeitsblätter aufheben und erneut schützen, wenn VBA mit ihnen interagieren muss:

Sub Blatt1_Bearbeiten()
    'Den Schutz von Blatt1 aufheben 
    Worksheets("Blatt1").Unprotect

    'Etwas mit Blatt1 machen

    'Blatt1 wieder schützen
    Worksheets("Blatt1").Protect
End Sub

Man kann jedoch leicht vergessen, den Schutz aufzuheben und/oder den Schutz für die Arbeitsblätter wiederherzustellen. Dies kann die Wahrscheinlichkeit eines Kodierungsfehlers erhöhen. Stattdessen können Sie die Einstellung UserInterFaceOnly verwenden. Wenn diese Einstellung auf TRUE gesetzt ist, werden die Arbeitsblätter NUR vor Benutzern geschützt, NICHT aber vor VBA. Ihr VBA-Code kann das Arbeitsblatt genauso bearbeiten, wie wenn es nicht gesperrt wäre. Zwei wichtige Punkte zu UserInterFaceOnly:

• Diese Einstellung ist nicht über das Menü Arbeitsblattschutz (siehe oben) verfügbar. Es handelt sich um eine Einstellung, die in VBA definiert werden muss.
• Die Einstellung wird nicht gespeichert, wenn Sie eine Arbeitsmappe schließen. Sie muss jedes Mal, wenn eine Arbeitsmappe geöffnet wird, neu definiert werden.

Um die Eigenschaft UserInterFaceOnly zu setzen, sollten Sie die folgende Workbook_Open-Ereignisprozedur im Modul ThisWorkbook platzieren:

Private Sub Workbook_Open()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True
    Next ws
End Sub

Workbook_Open ist eine spezielle Ereignisprozedur, die jedes Mal ausgeführt wird, wenn die Arbeitsmappe geöffnet wird. Sie muss im Modul ThisWorkbook platziert werden. Alternativ können Sie auch die Ereignisprozedur Auto_Open verwenden (hier nicht behandelt).

Makro – Den Schutz bei allen Blättern aufheben

Dieses Makro hebt den Schutz aller Blätter in einer Arbeitsmappe auf:

' den Schutz bei allen Blättern aufheben
Sub AlleBlaetterSchutzAufheben()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Unprotect "Kennwort"
    Next ws

End Sub

Makro – Alle Blätter schützen

Dieses Makro schützt alle Blätter in einer Arbeitsmappe:

' Alle Arbeitsblätter schützen
Sub AlleBlaetterSchuetzen()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Protect "Kennwort"
    Next ws

End Sub