Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.10.2025 17:40:39
16.10.2025 17:25:38
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Zeilen nach Datum sperren

Zeilen nach Datum sperren
11.03.2015 09:30:25
Alexander
Hallo zusammen,
ich habe einen Schichtplan und es sollen keine rückwirkenden Änderung > 2 Tage möglich sein.
In Zelle A1 befindet sich das heutige Datum "=HEUTE()"und in Zellen A3, A4, A5, usw. vortlaufend, das Datum der Tage für das Jahr 2015.
Nun Habe ich einen VBA-Code gefunden der schon fast das macht was ich brauche, nämlich er soll die Zeilen sperren, die das Datum des heutigen Datums.
Zusätzlich benötige ich noch eine Möglichkeit diesen Code für alle Tabellen zu nutzen (Tabelle1, Tabelle2, Tabelle3, usw.) und
Der Code sieht im Moment so aus:
Private Sub Workbook_Open()
With Sheets(1) 'anpassen  Anforderung: soll auch auf Tabelle2, 3, 4, usw. angwendet werden kö _
nnen
.Unprotect "x" 'passwort "x" ist optional
.Cells.Locked = True 'alle zelle sperren
Rowy = Columns("a:A").Find(Date, after:=.[a1]).Row 'Hier benötige ich ab  HEUTE() -2 sollen editierbar sein
Rows(Rowy).Locked = False
.Protect "x"
End With
End Sub

Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeilen nach Datum sperren
11.03.2015 12:39:29
Nepumuk
Hallo,
wenn du uns mehr über deinen Tabellenaufbau verraten würdest, dann täten wir uns leichter. Denn die Suche nach einem Datum größer bzw. kleiner einem bestimmten Datum funktioniert mit der Find-Methode nicht wirklich. Außer es macht dir nichts aus nach dem Öffnen der Mappe erst mal eine Minute zu warten bis die Suche abgeschlossen ist.
Gruß
Nepumuk

Anzeige
AW: Zeilen nach Datum sperren
11.03.2015 14:49:25
Klexy
Diesen Code in das VBA-Feld jedes Tabellenblatts, das geprüft werden soll, reinkopieren
Private Sub Worksheet_Activate()
Dim AlleA As Range
Set AlleA = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 1))
ActiveSheet.Unprotect "x" 'passwort "x" ist optional
ActiveSheet.Cells.Locked = False ' erstmal alles entsperren
For Each Cell In AlleA
If Cell.Value 

Anzeige
AW: Zeilen nach Datum sperren
11.03.2015 15:16:01
Nepumuk
Hallo Klexy,
das ist doch Unsinn. Oder hast du Lust bei jedem umschalten von einer Tabelle in eine andere 2-3 Sekunden zu warten bis dein Makro fertig ist? Wenn die Tabellenstruktur bekannt ist dann kannst du innerhalb von Bruchteilen einer Sekunde die Position der Kalenderdaten berechnen und die entsprechenden Zellen entsperren.
Gruß
Nepumuk

Anzeige
AW: Zeilen nach Datum sperren
12.03.2015 08:58:17
Klexy
Das ist nicht Unsinn, sondern die Anforderung.
Außerdem hat die Tabelle maximal 366 Zeilen, was nur Sekundenbruchteile in Anspruch nimmt. Wenn dieses Makro bei dir 2-3 Sekunden in Anspruch nimmt, solltest du vielleicht daran denken, denen Pentium 1 gegen einen Pentium 2 auszuwechseln.

Anzeige
AW: Zeilen nach Datum sperren
12.03.2015 09:17:28
Nepumuk
Hallo,
und wenn ich an einem Tag 20mal zwischen zwei Tabellen wechsle dann läuft deine Prozedur 38mal für die Katz. Das ist Unsinn.
Gruß
Nepumuk
PS. Mein Rechner arbeitet mit 3,4 GHz

AW: Zeilen nach Datum sperren
12.03.2015 20:09:05
Volker
Hallo Zusammen,
darf ich mich einmal kurz in diese Diskussion zwischengrätschen? Ich hoffe, dies ist nicht zu unverschämt.
Ich war und bin auf der Suche nach der Lösung einer anderen Aufgabenstellung. Ich möchte den Eingabebereich einer Tabelle dahingend schützen, dass die Zellen, in denen zuvor Eingaben erfolgt sind, beim Speichern geschützt und dementsprechend nicht ausversehen vom nächsten User überschrieben werden können Hierzu habe ich ein passendes script gefunden, haber allerdings selbst keine Ahnung von der Marterie. Dieses Script (Teil Arbeitsmappe, teils eigenes Modul) funktioniert super, leider habe ich das Problem, dass in der Tabelle verbundene Zellen enthalten sind, was zur Fehlermeldung führt. Das Ansteuern der verbundenen Zellen stellt wohl einen größeren Aufwand dar und wird auch von dem ein oder anderen Experten aus wohl guten Gründen abgleht. Jetzt der Grund, warum ich mich hier so unverschämt einmische: Kann man das Problem nicht dadurch lösen, dass direkt die ganze Zeile gesperrt wird. Ob einzele Zellen oder die Zeilen die betroffen sind, wäre mir ja egal. Mein minimaler Sachverstand sagt mir aber, dass die Lösung für mich sein könnte.
Habt Ihr einen Anpassungsvorschlag für mich?
Hier der Code und schon einmal vielen, vielen Dank und nochmals sorry, für die Intervention:
Gruß,
Volker
Arbeitsmappe:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If MsgBox("Achtung! Nach dem Speichern können die Einträge nicht mehr verändert werden!",  _
vbOKCancel, _
" WARNUNG!!!") = vbOK Then
Call Schutzweg
Call Zellen_schützen
Call Schutz
Else
Cancel = True
End If
End Sub

Modul:
Sub Schutz()
ActiveSheet.Protect ("Passwort"), DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub Sub Schutzweg()
ActiveSheet.Unprotect ("Passwort")
End Sub Sub Zellen_schützen()
Dim Zelle As Range
For Each Zelle In Range("E18:i100")
If Zelle.Value "" Then
Zelle.Locked = True
Zelle.FormulaHidden = True
End If
Next Zelle
End Sub

Anzeige
AW: Zeilen nach Datum sperren
13.03.2015 06:13:41
Alexander
Bitte Volker, da es sich um eine andere Problematik handelt als meine, eröffne einen neuen Thread.
Danke.
Gruß,
Alex

AW: Zeilen nach Datum sperren
16.03.2015 13:55:31
Klexy
Die ganze Teile kann man so sperren:
Zelle.EntireRow.Locked = True
Bau das mal ein, sollte funktionieren.

AW: Zeilen nach Datum sperren
12.03.2015 09:40:17
Alexander
Vielen Dank für die schnelle Hilfe!
Dein Code funktioniert erst mal, echt super! Doch ich glaube, da habe ICH zu kurz gedacht.
1. Vielleicht hat Nepumuk recht und ich sollte mal mehr über die Struktur unseres Schichtplanes mitteilen.
Userbild
2. Unser Schichtplan ist einigermaßen komplex, wir haben ca. 100 Mitarbeiter in 4 verschiedenen Bereichen (Tabellenblättern)
3. Der Plan ist derzeit so geschützt, dass Schichtleiter nur bestimmte Bereiche (Grün markiert), unter Verwendung eines eigenen Schichtleiterpassworts (xxx) bearbeiten können. Und die Administratoren, welche alles ändern können müssen, den Blattschutz mit dem Passwort (yyy)verwenden. Dies alles sind reine Excel-Fukntionen.
Nun kollediert natürlich dein VBA Code, mit den, von den Administratoren gesetzten Blattschutz, so dass die Schichtleiter, nach dem Datum > HEUTE() -2, auch alles verändern können. Das darf natürlich nicht sein.

Anzeige
AW: Zeilen nach Datum sperren
12.03.2015 11:21:33
Klexy
Never Change a running System.
Das mit den zweistufigen Berechtigungen könnte vom Makro auch abgefangen werden, wenn die Anforderungen klar sind.
Aber warum sollen denn die Zellen freigegeben werden, wenn die dann doch nix ändern dürfen?

AW: Zeilen nach Datum sperren
12.03.2015 11:40:19
Alexander
Die Schichtleiter, dürfen die Schicht im voraus planen, d. h höchstens 2 Tage rückwirkend dürfen Änderungen vorgenommen werden, dann nicht mehr.
Administratoren mit Passwort (yyy)dürfen alles bearbeiten, auch rückwirkend und die Schichtleiter mit Passwort (xxx) nur den grünen Bereich, aber höchstens 2 Tage rückwirkend.

Anzeige
AW: Zeilen nach Datum sperren
16.03.2015 13:51:50
Klexy
Mein Makro sperrt doch alles, was älter als 2 Tage ist. Also passend für die Schichtleiter, oder?
Die Schichtleiter brauchen in dieser Lösung gar kein Passwort, weil das Makro schon alles macht. Die Administratoren können dann mit dem Passwort rein. Es gibt nur das eine Passwort, das im Makro enthalten ist.

Anzeige
AW: Zeilen nach Datum sperren
16.03.2015 15:03:33
Alexander
Ja, es sperrt alles was < HEUTE() -2 Tage ist, aber alles was nach dem besagten Datum ist, ist freigegeben. D. h. Schichtleiter können "ALLES" nach besagten Datum bearbeiten, obwohl sie nur den grünen Bereich bearbeiten dürfen.(also auch Formeln außerhalb des grünen Bereichs) Das kommt davon, dass das Makro den Blattschutz aushebelt, der alles sperrt, außer den bearbeitbaren Bereich (ExcelFunktion "Benutzer dürfen Bereiche bearbeiten") für die Schichtleiter.

Anzeige
AW: Zeilen nach Datum sperren
17.03.2015 12:34:14
Klexy
Das war aber nicht deine Anforderung. Ich zitiere: "...was ich brauche, nämlich er soll die Zeilen sperren, die das Datum Wenn sie nur den grünen Bereich bearbeiten sollen, dann muss man EntireRow durch den entsprechenden Zellbereich ersetzen.
Ich hab das Makro darauf angepasst:
Private Sub Worksheet_Activate()
Dim AlleA As Range
Set AlleA = Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 1))
ActiveSheet.Unprotect "x" ' statt "x" das Administratoren-Passwort eintragen
ActiveSheet.Cells.Locked = True ' erstmal alles sperren
For Each Cell In AlleA
If Cell.Value > Range("A1").Value - 2 Then ' wenn mindestens 2 kleiner als A1
'Cell.EntireRow.Locked = False ' diese gesamte Zeile entsperren
Range(Cell.Offset(0, 3), Cell.Offset(0, 14)).Locked = False ' die 12 Zellen neben  _
dem Datum entsperren
End If
Next Cell
ActiveSheet.Protect "x" ' hier auch
End Sub

Anzeige
AW: Zeilen nach Datum sperren
17.03.2015 19:31:10
Alexander
Hallo Klexy,
danke noch mal für deine Arbeit! Wahrscheinlich drücke ich mich falsch aus, jetzt funktioniert der grüne Bereich, das ist erst mal sehr gut, doch jetzt können die Schichtleiter auch rückwirkend > 2 Tage bearbeiten, da die "IF-Anweisung" (Range(Cell.Offset(0, 3), Cell.Offset(0, 14)).Locked = False) die ganze Spalte 3 - 14 frei gibt. Sie soll die Spalte 3 - 14 freigeben, doch nur > HEUTE() -2 Tage.
Sorry für die viele Arbeit und dass ich mich wahrscheinlich zu kompliziert ausdrücke. Ich weiß nicht wie ich's besser erklären kann.

Anzeige
AW: Zeilen nach Datum sperren
17.03.2015 23:06:02
Klexy
Das Makro gibt nur Zellen mit den folgenden Eigenschaften frei, und zwar wenn alle 3 Eigenschaften gleichzeitig zutreffen:
- es ist ein Eintrag in Spalte A vorhanden
- der Eintrag ist > A1 -2 Tage, also gestern, heute, morgen, übermorgen usw.
- und die Spalten sind zwischen C und O
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Zeilen nach Datum sperren in Excel


Schritt-für-Schritt-Anleitung

Um Zeilen in Excel nach einem bestimmten Datum zu sperren, kannst du den folgenden VBA-Code verwenden. Dieser Code sorgt dafür, dass alle Zeilen, deren Datum älter als zwei Tage ist, gesperrt werden.

  1. Öffne Excel und gehe zu dem Arbeitsblatt, in dem du die Zeilen sperren möchtest.
  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  3. Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.
  4. Füge den folgenden Code ein:
Private Sub Workbook_Open()
    Dim Rowy As Long
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect "x" ' Passwort "x" ist optional
        ws.Cells.Locked = True ' Alle Zellen sperren
        Rowy = ws.Columns("A:A").Find(Date, after:=ws.Cells(1, 1)).Row ' Suche nach HEUTE()
        ws.Rows("1:" & Rowy - 1).Locked = False ' Zeilen freigeben bis HEUTE() - 2
        ws.Protect "x" ' Blattschutz aktivieren
    Next ws
End Sub
  1. Schließe den VBA-Editor und speichere die Arbeitsmappe als Excel-Arbeitsmappe mit Makros (*.xlsm).
  2. Schließe und öffne die Arbeitsmappe erneut, um den Code auszuführen.

Häufige Fehler und Lösungen

  • Fehler: Der Code funktioniert nicht.
    Stelle sicher, dass du die Arbeitsmappe im richtigen Format gespeichert hast und dass die Makros aktiviert sind.

  • Fehler: Das Datum wird nicht gefunden.
    Überprüfe, ob das Datum im richtigen Format in der Spalte A vorliegt. Excel erwartet standardmäßig das Datumsformat.

  • Problem: Der Schutz funktioniert nicht wie gewünscht.
    Achte darauf, dass das Passwort korrekt eingegeben wird und dass der Blattschutz aktiviert ist, bevor du die Zellen sperrst.


Alternative Methoden

Eine andere Methode, um Zeilen zu sperren, ist die Verwendung von Excel-Funktionen:

  1. Du kannst die Funktion WENN in Kombination mit einer bedingten Formatierung nutzen, um visuell darzustellen, welche Zeilen gesperrt sind.
  2. Verwende eine Formel, um das Datum zu überprüfen und die Zeilenhöhe mit VBA zu sperren:
If Cell.Value < Date - 2 Then
    Cell.EntireRow.Hidden = True ' Zeilenhöhe sperren
End If

Praktische Beispiele

  1. Schichtplan: Du hast einen rollierenden Schichtplan und möchtest, dass Schichtleiter nur Änderungen für die nächsten zwei Tage vornehmen können. Der oben genannte VBA-Code kann dies ermöglichen.

  2. Verbundene Zellen: Wenn deine Tabelle verbundene Zellen enthält, kannst du die gesamte Zeile sperren, um Fehler zu vermeiden. Ersetze Cell.EntireRow.Locked = True im Code durch:

Cell.EntireRow.Locked = True ' Ganze Zeilen sperren

Tipps für Profis

  • Passwortsicherheit: Verwende starke Passwörter für den Blattschutz, um unbefugten Zugriff zu verhindern.
  • Makros regelmäßig testen: Teste deinen VBA-Code in einer Kopie deiner Arbeitsmappe, um ungewollte Änderungen zu vermeiden.
  • Dokumentation: Halte eine Dokumentation über die verwendeten Makros und deren Funktionalität, um zukünftige Änderungen zu erleichtern.

FAQ: Häufige Fragen

1. Wie kann ich die Zeilenhöhe in Excel sperren?
Um die Zeilenhöhe zu sperren, kannst du die Eigenschaft RowHeight im VBA-Code anpassen. Setze die Zeilenhöhe auf eine feste Zahl, um Änderungen zu verhindern.

2. Ist es möglich, mehrere Tabellenblätter gleichzeitig zu schützen?
Ja, indem du eine Schleife über alle Arbeitsblätter in deiner Arbeitsmappe erstellst, wie im obigen Beispiel gezeigt.

3. Welche Excel-Version benötige ich für die Verwendung von VBA?
Alle modernen Excel-Versionen, die VBA unterstützen (z.B. Excel 2010 und neuer), können für diese Funktionalität verwendet werden.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige