gesperrt Häckchen übernehmen

Question

Hallo,

kurz ein paar Daten:

Format für A1 ist gesperrt aber nicht ausgeblendet

Formal für A2 ist nicht gesperrt und auch nicht ausgeblendet

in A1 steht zb. folgende Formel:

=b1

Jetzt kommt ein Blattschutz drauf und A1 ist gesperrt und man kann die Formel sehen und auch in A2 kopieren/nach unten ziehen.

Das Problem ist, das A2 nicht das selbe Format hat wie A1. Wenn ich zb. A1 gelb mache und dann nach unten ziehe übernimmt er das gelb und die Formel aber nicht das gesperrt Häckchen. A2 ist somit ungeschützt und die Formel könnte versehentlich gelöscht werden.

Die Tabelle soll also nach Bedarf erweiert und automatisch gesperrt werden.

Ist das irgendwie machbar?


Gruß
energun222

Answer 1

Hallo energun,

das ist von Excel durchaus so gewollt. Ist das Blatt einmal geschützt muss Excel wissen, wie es auf welche Zelle zugreifen soll. Wenn du das umgehen willst fällt mir nur eine Makrolösung ein.

Gib den folgenden Code in das entsprechende Tabellenmodul deines VBA-Editors ein:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If ActiveSheet.ProtectionMode = True And Target.Locked = False _
And Target.Value <> "" And Target.Offset(-1, 0).Locked = True Then
ActiveSheet.Unprotect "Passwort"
Target.Locked = True
ActiveSheet.Protect "Passwort"
End If
End Sub

Dabei spielt es übrigens keine Rolle, ob du den Wert runterziehst, oder manuell eingibst. Das Häkchen wird nach Eingabe von der überliegenden Zelle übernommen, sofern diese schreibgeschützt ist.

Gruß Mr. K.

Answer 2

Hallo xlKing,

es funktioniert zwar nur macht es mehr als es sollte. :-)

Es setzt das gesperrt Häkchen auch dort wo obendrüberliegende Zelle garkeins hat.

Ich habe mal eine Beispielmappe hochgeladen. Alles was rot ist hat das Häkchen und das was grün ist hat es nicht. Sobal du jetzt den Code einsetzt ist das erste grüne Feld gesperrt.

https://www.dropbox.com/s/58jtthi5ps1gh5h/test_blattschutz.xlsm?dl=0


Gruß
energun222

Answer 3

Mein Fehler. Im SelectionChange-Ereignis kann Target im Gegensatz zum Change-Ereignis auch Bereiche zurückgeben. Das hatte ich nicht bedacht. Sorry.

Hier der angepasste Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Ende

If ActiveSheet.ProtectionMode = True And Target.Locked = False _
And Target.Cells(Target.Cells.Count).Value <> "" And Target.Offset(-1, 0).Locked = True Then
ActiveSheet.Unprotect "Passwort"
Target.Locked = True
ActiveSheet.Protect "Passwort"
End If
Ende:
End Sub

Meine andere Aussage oben ist übrigens auch nicht ganz richtig. Der Code wird nicht nach Eingabe sondern bei Zellwechsel (oder eben nach Runterziehen) ausgelöst. Für das automatische Sperren nach Eingabe benötigt man einen weiteren Code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "" Then
ActiveSheet.Unprotect "Passwort"
Target.Locked = True
ActiveSheet.Protect "Passwort"
End If
End Sub

Den aber nur bei Bedarf hinzufügen.

Answer 4

Hallo,

wenn der Blattschutz nicht aktiv ist und ich die gesperrte Zellen nach unten ziehe und danach den Blattschutz aktiviere sind die Zellen gesperrt was ja richtig ist. Der Blattschutz ist aber immer aktiv, ziehe ich nun die gesperrten Zellen nach unten passiert eigentlich garnichts.

Gruß
energun222

Answer 5

Hallo energun,

so, habe den Code nochmal ein wenig umgeschrieben und an dein Beispiel angepasst. Jetzt
kannst du auch ganze Zeilen nach unten ziehen. Das macht das Ganze einfacher.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveSheet.ProtectContents Then Schutz = True

If Target.Rows.Count > 1 Then
For Each zc In Target.Rows(Target.Rows.Count).Cells
If zc.Interior.Color = Cells(Target.Row, zc.Column).Interior.Color Then
If Schutz Then ActiveSheet.Unprotect "Passwort"
zc.Locked = Cells(Target.Row, zc.Column).Locked
If Schutz Then ActiveSheet.Protect "Passwort"
End If
Next zc
End If
End Sub

Probiers bitte nochmal aus. Bei mir funktioniert das prima.

Gruß Mr. K.

Answer 6

Ersetze noch die Zeile
For Each zc In Target.Rows(Target.Rows.Count).Cells
durch
For Each zc In Target.Cells
sonst wird nur die letzte Zeile der Markierung gesperrt.

Answer 7

Hallo xlKing,

es funktioniert bisher wunderbar. Freu

Vielen Dank für deine Mühen.

Gruß
energun222