Blattschutz für mehrere Tabellenblätter gleichzeitig ein-/ausschalten mit Auswahl, was erlaubt ist

Question

Hallo,

ich habe eine monatliche Datei, in der jeder Arbeitnehmer ein Tabellenblatt hat. Darin werden die täglichen Arbeiten, Stunden, Zuschläge usw. erfasst. Es dient dann als Vorlage für die Lohnabrechnung. Mehrere Mitarbeiter haben Zugriff auf die Datei und tragen die Daten ein (nie gleichzeitig, einer erfasst, einer nimmt mal Änderungen vor, ein anderer erfasst bei Abwesenheitsvertretung etc.). Ich habe die Datei komplett für alle Datenblätter durchformatiert, damit alle Blätter einheitlich sind. Nun habe ich eine liebe Kollegin, die mir jedes Mal, wenn sie in der Datei war, alle meine Formatierungen (von meistens eingerückter Rechtsbündigkeit) auf zentriert ändert. Nachdem ich nach meinem Urlaub nun die Datei wieder öffnete, hatte sie im aktuellen Monat, in der Vorlage für alle Monate und in der Datei mit dem kommenden Monat, die ich erst letzte Woche erstellte, alle Formatierungen wieder geändert. Das möchte ich nun für die Zukunft verhindern und habe überall bei jedem Mitarbeiter einen Blattschutz mit Passwort hinterlegt (danke an die vielen Beiträge im Internet für die Hilfen). Ich habe ein paar Spalten mit Formeln, die auch immer wieder gelöscht wurden, nun gesperrt. Insgesamt soll das einzelne Blatt aber nicht komplett geschützt, sondern bearbeitbar sein, da die anderen Personen ja Änderungen vornehmen oder bei Vertretung alles eintragen müssen. Durch die Auswahl beim Blattschutz konnte ich erreichen, dass gesperrte Zellen nicht bearbeitet werden dürfen, aber Zeilen formatiert (das ist wichtig, weil manchmal die Zeile zweizeilig werden muss) und die Daten eingetragen werden können. Spalten und Zellen dürfen nicht mehr formatiert werden.

Soweit klappt alles. Nun habe ich aber immer wieder Änderungen, die alle Tabellenblätter (Mitarbeiter) betreffen. Deshalb suchte ich nach einer Möglichkeit, dass der Blattschutz nicht ständig manuell je Mitarbeiter ein- bzw. wieder ausgeschaltet werden muss. Dazu habe ich im Internet auch gute Anleitungen mit dem Erstellen von Makros mit VBA (ich habe keine Ahnung davon) gefunden. Das habe ich eingebaut und es funktioniert. Dachte ich. Bis ich in einem geschützten Tabellenblatt versuchte, die Zeilenhöhe zu verändern oder etwas einzutragen.

Mein Problem:
Dieses Makro berücksichtigt nicht, dass alles erlaubt sein soll außer gesperrte Zellen zu bearbeiten oder Zellen oder Spalten zu formatieren.

Jedes Mal, wenn ich den Blattschutz über das Makro aus- bzw. eingeschaltet habe, wurden meine Einstellungen bei jedem Tabellenblatt wieder zurückgesetzt. Wie bekomme ich es hin, dass nur der Blattschutz bei jedem Tabellenblatt ein- bzw. ausgeschaltet werden, aber sonst überall keine Veränderungen in den Berechtigungen vorgenommen werden? In diesem VBA gibt es Eigenschaften, aber da steht alles mögliche auf englisch drin und ich habe keine Ahnung, was das bedeutet. Ich dachte, ob man dort die Berechtigungen für das Makro festlegen kann, aber da bräuchte ich auch Hilfe.

Sorry, dass ich so viel geschrieben habe, ich hoffe, ich konnte dadurch genau erklären, was ich an Hilfe benötige.

Ich habe zwei Module angelegt
Modul 1 - Blattschutz ein:

Sub Blattschutz_ein()
'schützt alle Blätter dieser Datei
 Dim Blatt As Worksheet
 
 For Each Blatt In Worksheets
 Blatt.Protect "meinPasswort"
 Next Blatt
End Sub

Modul 2 - Blattschutz aus:

Sub Blattschutz_aus()
'Blattschutz aufheben
 Dim Blatt As Worksheet
 
 For Each Blatt In Worksheets
 Blatt.Unprotect "MeinPasswort"
 Next Blatt
End Sub

Bei einer anderen Datei habe ich es mit diesen Texten versucht, aber gleiches Ergebnis:

Modul 1 - alle Blätter Blattschutz ein:

Sub Alle_Blaetter_Blattschutz_EIN_M_Pw()
        Application.ScreenUpdating = False
    Dim shBlatt As Worksheet
    For Each shBlatt In ActiveWorkbook.Sheets
        shBlatt.Protect Password:="meinPasswort"
    Next shBlatt
        Application.ScreenUpdating = True
End Sub

Modul 2 - alle Blätter Blattschutz aus:

Sub Alle_Blaetter_Blattschutz_AUS_M_Pw()
        Application.ScreenUpdating = False
    Dim shBlatt As Worksheet
    For Each shBlatt In ActiveWorkbook.Sheets
        shBlatt.Unprotect Password:="meinPasswort"
    Next shBlatt
        Application.ScreenUpdating = True
End Sub

Vielen Dank schon mal für die Unterstützung hier.

Sabine

Answer 1

Hallo Sabine,

wenn du das Blatt mit deinem Code schützt, wird der Standard-Schutz aktiviert. Willst du z.B. die Formatierung von Zeilen zulassen, dann musst du das entsprechend hinzufügen:

Sub Blattschutz_ein()
'schützt alle Blätter dieser Datei
 Dim Blatt As Worksheet
 
 For Each Blatt In Worksheets
    Blatt.Protect "meinPasswort", AllowFormattingRows:=True
 Next Blatt

End Sub

Ansonsten erkläre mal, welche Haken du beim manuellen Schützen setzt bzw. nicht setzt.

Gruß

M.O.

Answer 2

Hallo M.O.,

es sind in allen Auswahlmöglichkeiten die Haken gesetzt, mit Ausnahme von

Gesperrte Zellen auswählen

Zellen formatieren

Spalten formatieren

Gruß
Sabine

Answer 3

Hallo Sabine,

schau mal, ob der Code das macht, was du willst:

Sub Blattschutz_ein()
'schützt alle Blätter dieser Datei
 Dim Blatt As Worksheet
 
 For Each Blatt In Worksheets
   With Blatt
    .Protect "meinPasswort", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, _
        AllowFiltering:=True, AllowUsingPivotTables:=True
     .EnableSelection = xlUnlockedCells
    End With
 Next Blatt

End Sub

Gruß

M.O.

Answer 4

Hallo M.O.,

recht herzlichen Dank für Ihre Hilfe!!!

Es klappt. Nun kann ich den Text von der "Probierdatei" in die Originaldateien kopieren.

Ein schönes, sonniges Wochenende wünscht

Sabine