Office: (Office 2010) Sperren von Zellen basierend auf Werten in einer anderen Zelle

Hallo zusammen,
ich brauche ein wenig "VBA-Hilfe" von den Fachleuten.
Folgenden Code habe ich mit meinem Halbwissen zusammen gebastelt.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G6") = "Offen" Or Range("G6") = "Splitting" Then
Range("C6,K6,M6:R6,T6:V6").Locked = False
ElseIf Range("G6") = "Abgeschlossen" Or Range("G6") = "Teilverkauf" Then
Range("C6,K6,M6:R6,T6:V6").Locked = True
End If
If Range("G7") = "Offen" Or Range("G7") = "Splitting" Then
Range("C7,K7,M7:R7,T7:V7").Locked = False
ElseIf Range("G7") = "Abgeschlossen" Or Range("G7") = "Teilverkauf" Then
Range("C7,K7,M7:R7,T7:V7").Locked = True
End If
If Range("G8") = "Offen" Or Range("G8") = "Splitting" Then
Range("C8,K8,M8:R8,T8:V8").Locked = False
ElseIf Range("G8") = "Abgeschlossen" Or Range("G8") = "Teilverkauf" Then
Range("C8,K8,M8:R8,T8:V8").Locked = True
End If
If Range("G9") = "Offen" Or Range("G9") = "Splitting" Then
Range("C9,K9,M9:R9,T9:V9").Locked = False
ElseIf Range("G9") = "Abgeschlossen" Or Range("G9") = "Teilverkauf" Then
Range("C9,K9,M9:R9,T9:V9").Locked = True
End If
End Sub

Ziel ist es, die Zellen einer Zeile ohne Blattschutz, nach Eingabe von zwei festgelegten Begriffen ("Abgeschlossen"; "Teilverkauf"), automatisch mit einem Schutz zu sperren.
Funktioniert auch erstmal, aber ...
1. Durch diesen Code funktionieren die "Rückgängig" und "Wiederherstellen" Butten nicht mehr (auch nicht über Strg+Z und Strg+Y).
Kann man diesen Fehler durch ändern des Codes beheben?
2. In meiner Exceltabelle bin ich mittlerweile in Zeile 70.
Gibt es eine Möglichkeit, den Code entsprechend anzupassen, dass er nicht unendlich lang wird?
Bei einer Lösung wären eins, zwei Worte der Erklärung noch hilfreich, möchte es gerne verstehen ...

Vielen Dank im Vorraus ...

 

Nein. nach Makro ist das nicht möglich.
Prüfe ob Spalte G, da brauchst Du weniger Zeilen. Arbeite mit Offset. Range("C" & Target.row, &"K" &Target.row ,"M" & Target.row &":R" &Target.row,&"T" & ":V" & Target.row9").Locked = True

 

Moin,

warum VBA?
Dein Vorhaben ist auch mit einer Gültigkeitsprüfung machbar.

Markiere deine Bereiche C6:C999,K6:K999,M6:R999,T6:V999 o.ä.
Wähle Menü Daten > Datentools > Datenüberprüfung
Zulassen: Benutzerdefiniert
Formel: =($G6<>"Abgeschlossen")*($G6<>"Teilverkauf")
(dazu muss die aktive Zelle in Zeile 6 sein, sonst die Formel entsprechend anpassen)

Über den Reiter "Fehlermeldung" kannst du sogar noch einen Text festlegen, der gezeigt wird, falls doch jemand versucht, in den entsprechenden Zellen Einträge zu tätigen.

 

Hallo Hajo,
danke für die schnelle Antwort, ich werde mal ausprobieren, ob bzw. wie ich deine Vorschläge umsetzen kann.

Gruß Micha

 

Hallo Schatzi,
leider ist mir beim Erstellen der Beispielsdatei ein Fehler unterlaufen, in der Originaldatei sind in einigen Spalten schon mittels Datenüberprüfung Dropdowns angelegt. Da die Dropdowndaten auf einem anderen Tabellenblatt liegen, sind sie im Beispiel leider nicht ordnungsgemäß übernommen wurden und von mir gelöscht. Sorry ...
Soweit ich weiß, gehen leider keine zwei Datenüberprüfungen auf eine Zelle.
Trotzdem vielen Dank für deine Antwort.

Gruß Micha

 

Natürlich geht das:
Die Zellen, die gesperrt/entsperrt werden sollen erhalten eine Datenüberprüfung mit der Formel
=($G6="Offen")+($G6="Splitting")
Wichtig: den Haken bei "leere Zellen ignorieren" entfernen!

 

Hallo HKindler,
danke für die Anregung. Ich wusste wirklich nicht, dass man mehrere Datenüberprüfungen verknüpfen kann.
Wieder etwas dazu gelernt.
Die Spalten 'K' und 'R' prüfe ich z.B. in der Originalliste auf 'Textlänge=5'.
Jetzt habe ich es entsprechend abgeändert, und es funktioniert.
=LÄNGE(K6)=5*($G6<>"Abgeschlossen")*($G6<>"Teilverkauf")
Ich hoffe, dass ich es in den anderen Spalten auch entsprechend hinbekomme ...
Vielen Dank noch mal an dich, Schatzi und Hajo für eure Hilfe ...

Gruß Micha