Zellen sperren wen Wert gößer als 1

[Hans Schubert]

Hallo Excel Gemeinde,

trotz Google habe ich die Lösung leider nicht gefunden.

Folgendes soll passieren.

Beim schließen der Datei soll in dem aktiven Blatt geprüft werden ob in
der Zelle a1 der wert größer als 1 ist. Ist dies der Fall soll der
Blattschutz aufgehoben und die Zellen a1 bis d1 gesperrt werden.
dann soll der wert in a2 geprüft werden. Ist er größer als 1 sollen die
Zellen a2 bis d2 gesperrt werden. Dies soll so lange weiter geführt
werden bis man auf eine leere Zelle in der A-Spalte trifft.(Etwa 50 Reihen)

Das Makro soll verhindern, dass nachträglich Daten in einem Arbeitsblatt
zu ändern nachdem dieses geschlossen wurde.

Nehme an für viele von Euch ist das ein Klacks, ich würde Wochen
brauchen um dies herauszufinden.
Vielen Dank vorab.

Hans

[Alexander Wolff]

Post by Hans Schubert
Beim schließen der Datei soll in dem aktiven Blatt geprüft werden ob in
der Zelle a1 der wert größer als 1 ist. Ist dies der Fall soll der
Blattschutz aufgehoben und die Zellen a1 bis d1 gesperrt werden.
dann soll der wert in a2 geprüft werden. Ist er größer als 1 sollen die
Zellen a2 bis d2 gesperrt werden. Dies soll so lange weiter geführt
werden bis man auf eine leere Zelle in der A-Spalte trifft.(Etwa 50 Reihen)
Das Makro soll verhindern, dass nachträglich Daten in einem Arbeitsblatt
zu ändern nachdem dieses geschlossen wurde.

Der Blattschutz (mit Kennwortvergabe ausreichend für Laien, nicht für
Newsgroupleser!) verhindert ja grad dieses.

Alle Zellen sind von Beginn an nämlich als gesperrt markiert, nicht etwa als
freigegeben. Erst aber wenn Du den Blattschutzriegel vorschiebst, tritt
dieser Schutz in Kraft. Das bedeutet, daß ohne Schutz ALLE Zellen noch
geändert werden können, egal ob gesperrt oder nicht.

Wenn Du jemandem, wie auch immer, die Eingabe ermöglichst: Wie willst Du die
Art der Eingaben dann kontrollieren oder unterbinden, AUSSER daß Du Dir eine
Kopie zurückbehältst und später mit der Datei vergleichst?

Du meinst also wahrscheinlich was anderes, als was ich verstanden habe.

Gruß Alexander

[Hans Schubert]

Post by Alexander Wolff
Du meinst also wahrscheinlich was anderes, als was ich verstanden habe.
Gruß Alexander

Hallo,

tatsächlich meine ich etwas anderes. Habe mich anscheinend nicht klar
genug ausgedrückt:
In der Tabelle sollen nur die Zeilen blockiert werden, in denen ein
Eintrag vorgenommen wurde. Diese Eintragungen sollen also später nicht
mehr rückgängig gemacht werden können.
Dennoch soll der Rest der Tabelle für Eingaben frei sein.

Hier noch einmal meine Vorstellungen:

Sub auto_close
Mit dem activen Blatt Blattschutz aufheben

Zu Zelle a1 gehen.
wenn wert in Zelle a1 größer ist als 1, dann a1 bis d1 sperren
zu Zelle a2 gehen (Offset Befehl)
wenn Wert in a2 größer als 1 ist, dann a2 bis d2 sperren
etc
etc
etc
Wenn Wert in a30 kleiner als 1 ist /(Beispiel),
soll das Makro die Durchsuchung beenden, oder die entsprechenden Zellen
nicht sperren.

Hoffe, das ganze war jetzt etwas klarer.

Gruß
Hans

[Alexander Wolff]

Post by Hans Schubert
In der Tabelle sollen nur die Zeilen blockiert werden, in denen ein
Eintrag vorgenommen wurde. Diese Eintragungen sollen also später nicht
mehr rückgängig gemacht werden können.
Dennoch soll der Rest der Tabelle für Eingaben frei sein.
Sub auto_close
Mit dem activen Blatt Blattschutz aufheben
Zu Zelle a1 gehen.
wenn wert in Zelle a1 größer ist als 1, dann a1 bis d1 sperren
zu Zelle a2 gehen (Offset Befehl)
wenn Wert in a2 größer als 1 ist, dann a2 bis d2 sperren
etc
etc
etc
Wenn Wert in a30 kleiner als 1 ist /(Beispiel),
soll das Makro die Durchsuchung beenden, oder die entsprechenden
Zellen nicht sperren.

Hmm. Wer schützt das Blatt?

Falls ohne Kennwort: Wie willst du Änderungen verhindern?
Falls mit Kennwort: Wie willst du Änderungen durch Fortgeschrittene
verhindern?

Diese Fragen stehen für mich im Vordergrund.

Ansonsten:
Du mußt nach Strg-A erst mal Format Zellen Schutz Gesperrt aufheben.
Danach kann dann ein Makro die von Dir gewünschten Sperrungen durchführen.
Aber mit Kennwort schützen kann es das Blatt nicht sinnvoll, da man das
Kennwort aus dem Code ersehen kann. Und an dieser Stelle kannst Du diese
Antwort noch mal von oben lesen :)

Gruß Alexander

[Hans Schubert]

Post by Alexander Wolff

Post by Hans Schubert
In der Tabelle sollen nur die Zeilen blockiert werden, in denen ein
Eintrag vorgenommen wurde. Diese Eintragungen sollen also später nicht
mehr rückgängig gemacht werden können.
Dennoch soll der Rest der Tabelle für Eingaben frei sein.
Sub auto_close
Mit dem activen Blatt Blattschutz aufheben
Zu Zelle a1 gehen.
wenn wert in Zelle a1 größer ist als 1, dann a1 bis d1 sperren
zu Zelle a2 gehen (Offset Befehl)
wenn Wert in a2 größer als 1 ist, dann a2 bis d2 sperren
etc
etc
etc
Wenn Wert in a30 kleiner als 1 ist /(Beispiel),
soll das Makro die Durchsuchung beenden, oder die entsprechenden
Zellen nicht sperren.

Hmm. Wer schützt das Blatt?
Falls ohne Kennwort: Wie willst du Änderungen verhindern?
Falls mit Kennwort: Wie willst du Änderungen durch Fortgeschrittene
verhindern?
Diese Fragen stehen für mich im Vordergrund.
Du mußt nach Strg-A erst mal Format Zellen Schutz Gesperrt aufheben.
Danach kann dann ein Makro die von Dir gewünschten Sperrungen durchführen.
Aber mit Kennwort schützen kann es das Blatt nicht sinnvoll, da man das
Kennwort aus dem Code ersehen kann. Und an dieser Stelle kannst Du diese
Antwort noch mal von oben lesen :)
Gruß Alexander

Hallo Alexander

eigentlich wollte ich einen Vorschlag, wie das Makro auszusehen hat,
das Blatt kann durchaus per Kennwort geschützt werden.
Den Code kann man ja auch durch Kennwort schützen.
Dass diese Kennwörter mit entsprechenden Tools auszulesen sind ist mir
klar, doch reichen mir diese Sicherheiten.

Irgendeinen Vorschlag, wie das Makro auszusehen hat, ohne dass ich diese
Vorgaben für jede Zeile einzeln eingeben muß??

Mir fehlen die genauen Offset- Befehle nicht bekannt.

[Alexander Wolff]

Post by Hans Schubert
Irgendeinen Vorschlag, wie das Makro auszusehen hat, ohne dass ich
diese Vorgaben für jede Zeile einzeln eingeben muß??

Sub SelektiverZellschutz()
'schützt 4 Spalten in Zeile, wo SpalteA >1
ActiveSheet.Unprotect
For i = 1 To 2 ^ 16
If Cells(i, 1) > 1 Then _
Range(Cells(i, 1), Cells(i, 4)).Locked = True
If Cells(i, 1) = "" Then i = 2 ^ 16
Next
ActiveSheet.Protect
End Sub

Gruß Alexander

[Hans Schubert]

Post by Alexander Wolff

Post by Hans Schubert
Irgendeinen Vorschlag, wie das Makro auszusehen hat, ohne dass ich
diese Vorgaben für jede Zeile einzeln eingeben muß??

Sub SelektiverZellschutz()
'schützt 4 Spalten in Zeile, wo SpalteA >1
ActiveSheet.Unprotect
For i = 1 To 2 ^ 16
If Cells(i, 1) > 1 Then _
Range(Cells(i, 1), Cells(i, 4)).Locked = True
If Cells(i, 1) = "" Then i = 2 ^ 16
Next
ActiveSheet.Protect
End Sub
Gruß Alexander

Hallo Alexander,

das funktioniert toll, aber leider nicht mit meinem Datenblatt.
Ich habe in einigen Zellen eine Gültigkeitsbeschränkung drin.
Kann das daran liegen?
Kann ich Dir das Blatt mal per PM schicken, villeicht kommst Du ja auf
den Trichter?
Gruß
Hans

[Alexander Wolff]

Post by Hans Schubert

Post by Alexander Wolff

Post by Hans Schubert
Irgendeinen Vorschlag, wie das Makro auszusehen hat, ohne dass ich
diese Vorgaben für jede Zeile einzeln eingeben muß??

Sub SelektiverZellschutz()
'schützt 4 Spalten in Zeile, wo SpalteA >1
ActiveSheet.Unprotect
For i = 1 To 2 ^ 16
If Cells(i, 1) > 1 Then _
Range(Cells(i, 1), Cells(i, 4)).Locked = True
If Cells(i, 1) = "" Then i = 2 ^ 16
Next
ActiveSheet.Protect
End Sub

das funktioniert toll, aber leider nicht mit meinem Datenblatt.
Ich habe in einigen Zellen eine Gültigkeitsbeschränkung drin.
Kann das daran liegen? Kann ich Dir das Blatt mal per PM schicken,
villeicht kommst Du ja auf den Trichter?

Na ja. Ich mach doch nur den Schutz an. Mit Gültigkeit hat das nix zu tun.

Schicke das Blatt, wenn Du willst. Ich guck ma.

Gruß Alexander

[Alexander Wolff]

Sub SelektiverZellschutz()

'schützt 4 Spalten in Zeile, wo SpalteA >1
'Gültigkeit wird gelöscht, damit nicht änderbar
'Empfehlung: Blattkopie für nächsten Auftrag vorhalten

ActiveSheet.Unprotect
For i = 2 To 2 ^ 16
If Cells(i, 1) > 0 Then
With Range(Cells(i, 1), Cells(i, 4))
.Locked = True
With .Validation
.Delete
.Add Type:=xlValidateInputOnly, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
End If
If Cells(i, 1) = "" Then i = 2 ^ 16
Next
ActiveSheet.Protect
End Sub

[Hans Schubert]

Post by Alexander Wolff
Sub SelektiverZellschutz()
'schützt 4 Spalten in Zeile, wo SpalteA >1
'Gültigkeit wird gelöscht, damit nicht änderbar
'Empfehlung: Blattkopie für nächsten Auftrag vorhalten
ActiveSheet.Unprotect
For i = 2 To 2 ^ 16
If Cells(i, 1) > 0 Then
With Range(Cells(i, 1), Cells(i, 4))
.Locked = True
With .Validation
.Delete
.Add Type:=xlValidateInputOnly, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End With
End If
If Cells(i, 1) = "" Then i = 2 ^ 16
Next
ActiveSheet.Protect
End Sub

Bingo, Passt und funzt,
Vielen Dank

Hans

[Peter Schürer]

Hallo Hans,

wenn Du möchtest, kann ich Dir mal eine Datei schicken, in der ich das
realisiert habe, was Du eigentlich möchtest.
Die Datei ist so richtig mit Menüleiste für die Anwendung und so.
Sollte übrigens auch für alle Anwender der alten "Colortronik" (Kunststoff)
interessant sein?

MfG
Peter

Post by Hans Schubert

Post by Alexander Wolff

Post by Hans Schubert
In der Tabelle sollen nur die Zeilen blockiert werden, in denen ein
Eintrag vorgenommen wurde. Diese Eintragungen sollen also später nicht
mehr rückgängig gemacht werden können.
Dennoch soll der Rest der Tabelle für Eingaben frei sein.
Sub auto_close
Mit dem activen Blatt Blattschutz aufheben
Zu Zelle a1 gehen.
wenn wert in Zelle a1 größer ist als 1, dann a1 bis d1 sperren
zu Zelle a2 gehen (Offset Befehl)
wenn Wert in a2 größer als 1 ist, dann a2 bis d2 sperren
etc
etc
etc
Wenn Wert in a30 kleiner als 1 ist /(Beispiel),
soll das Makro die Durchsuchung beenden, oder die entsprechenden
Zellen nicht sperren.

Hmm. Wer schützt das Blatt?
Falls ohne Kennwort: Wie willst du Änderungen verhindern?
Falls mit Kennwort: Wie willst du Änderungen durch Fortgeschrittene
verhindern?
Diese Fragen stehen für mich im Vordergrund.
Du mußt nach Strg-A erst mal Format Zellen Schutz Gesperrt aufheben.
Danach kann dann ein Makro die von Dir gewünschten Sperrungen durchführen.
Aber mit Kennwort schützen kann es das Blatt nicht sinnvoll, da man das
Kennwort aus dem Code ersehen kann. Und an dieser Stelle kannst Du diese
Antwort noch mal von oben lesen :)
Gruß Alexander

Hallo Alexander
eigentlich wollte ich einen Vorschlag, wie das Makro auszusehen hat,
das Blatt kann durchaus per Kennwort geschützt werden.
Den Code kann man ja auch durch Kennwort schützen.
Dass diese Kennwörter mit entsprechenden Tools auszulesen sind ist mir
klar, doch reichen mir diese Sicherheiten.
Irgendeinen Vorschlag, wie das Makro auszusehen hat, ohne dass ich diese
Vorgaben für jede Zeile einzeln eingeben muß??
Mir fehlen die genauen Offset- Befehle nicht bekannt.

[Thomas Ramel]

Grüezi Hans

Hans Schubert schrieb am 06.03.2005

Post by Hans Schubert
Beim schließen der Datei soll in dem aktiven Blatt geprüft werden ob in
der Zelle a1 der wert größer als 1 ist. Ist dies der Fall soll der
Blattschutz aufgehoben und die Zellen a1 bis d1 gesperrt werden.
dann soll der wert in a2 geprüft werden. Ist er größer als 1 sollen die
Zellen a2 bis d2 gesperrt werden. Dies soll so lange weiter geführt
werden bis man auf eine leere Zelle in der A-Spalte trifft.(Etwa 50 Reihen)
Das Makro soll verhindern, dass nachträglich Daten in einem Arbeitsblatt
zu ändern nachdem dieses geschlossen wurde.

Du kannst das Ganze auch ohne Makro erzielen:

--> Markiere B1:Dxx
--> Menü: 'Daten'
--> Gültigkeit
--> Register: 'Einstellungen'
--> Zulassen: 'Benutzerdefiniert'
--> Formel: =$A$1<=1
--> Register: 'Fehlermeldung' (eine solche kreieren)
--> [OK]

Nun können in den so 'bearbeiteten' Zellen nur noch Wert3e eingetragen
werden, wenn der Wert in Spalte A kleiner/gleich 1 ist, ansonsten sind sie
zur Eingabe gesperrt.
Falls sie doch nochmal bearbeitet werden sollen, dann kannst Du in Spalte A
einen Wert kleiner 1 eintragen.




Mit freundlichen Grüssen
Thomas Ramel

[Hans Schubert]

Post by Thomas Ramel
Grüezi Hans
Hans Schubert schrieb am 06.03.2005

Post by Hans Schubert
Beim schließen der Datei soll in dem aktiven Blatt geprüft werden ob in
der Zelle a1 der wert größer als 1 ist. Ist dies der Fall soll der
Blattschutz aufgehoben und die Zellen a1 bis d1 gesperrt werden.
dann soll der wert in a2 geprüft werden. Ist er größer als 1 sollen die
Zellen a2 bis d2 gesperrt werden. Dies soll so lange weiter geführt
werden bis man auf eine leere Zelle in der A-Spalte trifft.(Etwa 50 Reihen)
Das Makro soll verhindern, dass nachträglich Daten in einem Arbeitsblatt
zu ändern nachdem dieses geschlossen wurde.

--> Markiere B1:Dxx
--> Menü: 'Daten'
--> Gültigkeit
--> Register: 'Einstellungen'
--> Zulassen: 'Benutzerdefiniert'
--> Formel: =$A$1<=1
--> Register: 'Fehlermeldung' (eine solche kreieren)
--> [OK]
Nun können in den so 'bearbeiteten' Zellen nur noch Wert3e eingetragen
werden, wenn der Wert in Spalte A kleiner/gleich 1 ist, ansonsten sind sie
zur Eingabe gesperrt.
Falls sie doch nochmal bearbeitet werden sollen, dann kannst Du in Spalte A
einen Wert kleiner 1 eintragen.
Mit freundlichen Grüssen
Thomas Ramel

Hallo Thomas,

die Idee ist gut, ich werde mir das merken. Doch leider funzt das hier
nicht, da in Zellen schon eine andere Gültigkeit eigetragen ist, die
dann laut Excel erst gelöscht werden sollen.

Aber trotzdem danke.

Hans

[Thomas Ramel]

Grüezi Hans

Hans Schubert schrieb am 08.03.2005

Post by Hans Schubert
die Idee ist gut, ich werde mir das merken. Doch leider funzt das hier
nicht, da in Zellen schon eine andere Gültigkeit eigetragen ist, die
dann laut Excel erst gelöscht werden sollen.

Gegebenenfalls könnte man die Gültigkeiten kombinieren, indem die Formeln
zusammengelegt werden?



Mit freundlichen Grüssen
Thomas Ramel

[Hans Schubert]

Post by Thomas Ramel
Grüezi Hans
Hans Schubert schrieb am 08.03.2005

Post by Hans Schubert
die Idee ist gut, ich werde mir das merken. Doch leider funzt das hier
nicht, da in Zellen schon eine andere Gültigkeit eigetragen ist, die
dann laut Excel erst gelöscht werden sollen.

Gegebenenfalls könnte man die Gültigkeiten kombinieren, indem die Formeln
zusammengelegt werden?
Mit freundlichen Grüssen
Thomas Ramel

Hallo Thomas,

das bekomme ich leider nicht gerafft.
Die Gültigkeiten sollen z.Bsp nur Werte erlauben, welche in einer
Tabelle stehen.

Gruß
Hans

[Thomas Ramel]

Grüezi Hans

Hans Schubert schrieb am 08.03.2005

Post by Hans Schubert

Post by Thomas Ramel

Post by Hans Schubert
die Idee ist gut, ich werde mir das merken. Doch leider funzt das hier
nicht, da in Zellen schon eine andere Gültigkeit eigetragen ist, die
dann laut Excel erst gelöscht werden sollen.

Gegebenenfalls könnte man die Gültigkeiten kombinieren, indem die Formeln
zusammengelegt werden?

das bekomme ich leider nicht gerafft.
Die Gültigkeiten sollen z.Bsp nur Werte erlauben, welche in einer
Tabelle stehen.

Dann hast Du eine Gültigkeitsliste in den Zellen stehen?

Wenn Du auf den DropDown verzichen kannst (ich nehme an, dass dieser
verwendet wird), dann kannst Du meine Formel mit einem SVERWEIS()
kombinieren und auf diese Weise eine Eingabe nur zulassen wenn die
Bedingungen erfüllt sind und diese Eingaben sind dann auch auf die Liste
beschränkt.

Angenommen es geht erneut um den Bereich B1:Dxx und die Liste der erlaubten
Werte ist in F1:F10, dann kannst Du (bezogen auf B1) die folgende Formel
als 'Benutzerdefiniert' in der Gültigkeit verwenden:

=UND($A1<=1;NICHT(ISTNV(SVERWEIS(B1;$F$1:$F$10;1;0))))

Nun wird eine Eingabe verhindert wenn der Wert in Spalte A grösser 1 ist
und wenn der eingegebene Wert nicht in der Liste vorkommt.



Mit freundlichen Grüssen
Thomas Ramel

[Hans Schubert]

Post by Thomas Ramel
Grüezi Hans
Hans Schubert schrieb am 08.03.2005
Dann hast Du eine Gültigkeitsliste in den Zellen stehen?
Wenn Du auf den DropDown verzichen kannst (ich nehme an, dass dieser
verwendet wird), dann kannst Du meine Formel mit einem SVERWEIS()
kombinieren und auf diese Weise eine Eingabe nur zulassen wenn die
Bedingungen erfüllt sind und diese Eingaben sind dann auch auf die Liste
beschränkt.
Angenommen es geht erneut um den Bereich B1:Dxx und die Liste der erlaubten
Werte ist in F1:F10, dann kannst Du (bezogen auf B1) die folgende Formel

Hallo Thomas,

auf die Listeneinschränkung mit Dropdown möcht ich nach Möglichkeit
nicht verzichten, da die Geschichte sonst für die Eingebenden zu
kompliziert wird.

Besten Dank trotz allem für die Tips.

Gruß
Hans