Office: (Office 365) Zeilen automatisch sperren mittels VBA (wenn Bedingung erfüllt)

Moin zusammen,

folgendes Problem:
Im Normalfall verzichte ich aus Gründen auf den Einsatz von VBA, aber dieses Mal komme ich nicht umher. Ich habe schon fleißig recherchiert und mich versucht, aber komme nicht zum gewünschten Ergebnis.

Folgende Ausgangssituation:
Ich möchte bestimmte Zeilen einer Tabelle über den Bereich A?:AC? sperren, sofern in der Zelle AC? der Text "Yes" steht (wird über eine Formel automatisch eingetragen, wenn in der Zeile bestimmte Werte ausgefüllt werden. Hierzu habe ich bereits das Blatt mit dem Passwort "ente" geschützt und den Schutz (Zelle formatieren -> gesperrt) für den Tabellenbereich aufgehoben.

Mein kläglicher Versuch (funktioniert nicht...) sieht folgendermaßen aus:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("AZ1:BA1200"), Target) Is Nothing Then Exit Sub
If Target.Value = "Yes" Then
ActiveSheet.Unprotect "ente"
Target.EntireRow.Locked = True
ActiveSheet.Protect "ente"
End If
ActiveSheet.Protect "ente"

End Sub

Wer mag helfen und mir eine bessere Lösung anbieten / kann meinen Fehler identifizieren? :)
Gerne auch mit Erklärung, falls ihr meine Fehler aus der Ferne identifizieren könnt. Dann kann ich gleich noch was lernen und bin nächstes Mal nicht auf eure Hilfe angewiesen.

Danke
Theo

 

Hi,

- Worksheet_Change springt nur bei einem händischen Eintrag an, aber nicht, wenn sich der Wert aufgrund einer Formel ändert. Daher musst du die Zellen überwachen, auf die die Formel zugreift.

- du schreibst, dass dein "Yes" in Spalte AC steht, prüfst aber nur Zellen in AZ1:BA1200 auf "Yes"

- du brichst deine Routine ab, wenn keine Zelle von Target im genannten Bereich ist. Das ist spätestens dann eine schlechte Idee wenn du noch weitere Bereich auf etwas anderes überwachen willst. Daher besser

Code:

If Not Intersect(Range("AZ1:BA1200"), Target) Is Nothing Then
    'hier dein Code
End If

- deine Routine bricht mit einer Fehlermeldung ab, wenn mehrere Zellen gleichzeitig geändert werden.

 

Vielen Dank für deine Antwort! Das bringt schon mal ein bisschen Licht ins Dunkeln! :)
Dann haben wir ja den Übeltäter gefunden. Also muss ich die verformelte Abfrage quasi nochmal in VBA abbilden, richtig?

Dann muss ich prüfen, ob in AA UND AB jeweils ein Eintrag vom Typ "Testeintrag" erfolgt ist. Wie kann ich das abbilden (IF AND?!)? Die Zellen werden jeweils über ein Dropdown gefüllt, gilt das als händisch, sodass Worksheet_Change funktioniert?

Schönen Abend noch!

 

Hi,

klar könnte man jetzt mühsam das alles zusammensuchen, aber es geht auch einfacher Range(...).Precedents gibt die automatisch die Quell-Zellen zurück.
Ein Code könnte so aussehen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bereich As Range
Dim Zelle As Range
Set Bereich = Intersect(Target, Columns("AC").Precedents)
If Not Bereich Is Nothing Then
    For Each Zelle In Bereich
        Zelle.EntireRow.Locked = Cells(Zelle.Row, "AC") Like "Yes"
    Next Zelle
End If
End Sub

Mangels Beispieldatei natürlich ungetestet.