Office: Zellen einer Zeile anhängig von Eintrag in einer anderen Zelle sperren

Hallo zusammen.

folgendes Problem ist aufgetaucht. In einem Kalender steht in Spalte C das jeweilige Tagesdatum. In Spalte D steht entweder der Begriff "Schulferien" oder der Name eines Feiertages.

In den Spalten dahinter werden durch die Mitarbeiter entsprechend die Urlaubswünsche eingetragen.

Nun möchte ich erreichen, dass Einträge in den folgenden Spalten bis "EK" nicht möglich sind, wenn entweder das Tagesdatum ein Samstag oder Sonntag ist oder in Spalte D der Name eines Feiertages steht.

Ist das möglich?

Vielen Dank im Voraus
Beste Grüße

Christian

 

Hola,

das geht mit der Datenüberprüfung.

Du musst die Feiertage irgendwo in der Tabelle definieren, z.B. in Tabelle4 von A1 bis A10.

Dann markierst du die Spalten D bis EK und gibt bei Daten-Datenüberprüfung "Zulassen" Benutzerdefiniert folgende Formel ein:

Gruß,
steve1da

 

Hallo steve1da,

vielen Dank für Deine schnelle Antwort.
Ich habe leider bei meinen Ausführungen vergessen, dass in den einzelnen Zelle schon Gültigkeiten definiert sind. Es dürfen nur die Einträge aus den Zellen M1-M4 vorgenommen werden. Wie kann ich diese beiden Gültigkeitskriterien zusammenfassen?

Beste Grüße

Christian

 

Hola,

dann so:

Gruß,
steve1da

 

Hallo steve1da,

Hm. Dabei kann ich nirgendwo Eingaben mehr vornehmen. Habe allerdings trotzdem versucht, das für meine Bedürfnisse noch weiter zu entwickeln. In einem Bereich der Tabelle soll nämlich nur die linke Position feststehen. Danach soll die Eingabe frei verfügbar bleiben:

=ODER(WOCHENTAG($C9;2)>5;ZÄHLENWENN($EP$7:$EP$28;$D9)>0)=FALSCH;(LINKS(N9;1)=$M$1)+(LINKS(N9;1)=$M$2)+(LINKS(N9;1)=$M$3)+(LINKS(N9;1)=$M$4)

Leider bekomme ich hier immer eine Fehlermeldung, dass die Formel nicht stimmt, so dass ich nicht ausprobieren kann, ob die Eingaben grundsätzlich richtig wären.

Hast Du da eine Idee?

Gruß
Christian


Gruß
Christian

 

Hola,

ich hatte bei mir nicht zwischen C und D unterschieden.

Versuch es mal so:

Gruß,
steve1da

 

Hallo steve1da,

das hat leider auch nicht geklappt. Ich bekomme zwar keine Fehlermeldung mehr, dafür kann ich aber jetzt alles eintragen (auch über die eigentlichen vier Bereiche hinaus) und zwar sowohl in die Zellen, die dafür freigegeben sein sollen als auch in den Wochenend- und Feiertagszeilen.

An dem ersten "LINKS" in Deiner letzten Zeile liegt es nicht. Das Leerzeichen habe ich da entfernt.

Viele Grüße

Christian

 

Hola,

nicht das wir aneinander vorbeireden :) kannst du eine Beispieltabelle hochladen, dann bau ich das in deiner Datei nach.

Gruß,
steve1da

 

Hallo steve1da,

das wird heute nichts mehr. Werde ich erst am kommenden Montag schaffen. Ich weiß aber auch nicht, ob ich das so ohne weiteres hinbekomme, weil da viele Bezüge und viele persönliche Daten sind.

Melde mich einfach am Montag wieder.

Gruß

Christian

 

Hola,

eine reine Beispieldatei sollte ausreichen. Nur damit ich deinen Aufbau nachvollziehen kann.

Gruß,
steve1da

 

Moinsens!

Ich hab da mal einen anderen Ansatz als steve1da. Ich würde das ganze über ein VBA Makro lösen, das zumindest einmalig zur Aufbereitung der Tabelle aufgerufen werden müßte.



Der Code hinter dem Button da oben:

Code:

Private Sub CommandButton1_Click()

    Dim s1 As Worksheet
    Dim r1 As Range
    
    Dim y1 As Long
    Dim x1 As Integer
    Dim t1 As String
    
    Dim d1 As Date
    Dim f1 As String
    Dim b1 As Integer
    
    Dim ZelleSperren As Boolean
    Dim ZelleLoeschen As Boolean
    Dim ZelleFarbe As Long
    
    Set s1 = ActiveSheet
    s1.Unprotect ("abc")
    
s1.Cells.Interior.Color = xlColorIndexNone
s1.Cells.Borders.LineStyle = xlNone

    y1 = 3: x1 = 3 'start in c3
    's1.Activate: ActiveSheet.Cells(y1, x1).Activate
    t1 = s1.Cells(y1, x1).Value
    While (t1 <> "")
    
        Set r1 = s1.Range("e" & y1 & ":ek" & y1)
    
        d1 = s1.Cells(y1, 3).Value
        f1 = s1.Cells(y1, 4).Value
        b1 = 0 'werktag als default
        
        If (f1 = "Schulferien") Then b1 = 1 'schulferien
        If (Weekday(d1) = 6) Then b1 = 2 'samstag
        If (Weekday(d1) = 7) Then b1 = 3 'sonntag
        If (f1 <> "") And (b1 = 0) Then b1 = 4 'feiertag
        
        Select Case b1
            Case 0: 'werktag
                ZelleSperren = False
                ZelleLoeschen = False
                ZelleFarbe = RGB(239, 239, 239) 'blassgrau
            Case 1: 'schulferien
                ZelleSperren = False
                ZelleLoeschen = False
                ZelleFarbe = RGB(223, 223, 255) 'blassblau
            Case 2: 'samstag
                ZelleSperren = True
                ZelleLoeschen = True
                ZelleFarbe = RGB(223, 255, 223) 'blassgrün
            Case 3: 'sonntag
                ZelleSperren = True
                ZelleLoeschen = True
                ZelleFarbe = RGB(191, 255, 191) 'hellgrün
            Case 4: 'feiertag
                ZelleSperren = True
                ZelleLoeschen = True
                ZelleFarbe = RGB(255, 223, 223) 'blassrot
        End Select
        r1.Locked = ZelleSperren
        If (ZelleLoeschen) Then r1.ClearContents
        r1.Interior.Color = ZelleFarbe

        y1 = y1 + 1: x1 = 3 'start in c3
        's1.Activate: ActiveSheet.Cells(y1, x1).Activate
        t1 = s1.Cells(y1, x1).Value
    Wend
    
    Set r1 = s1.Range("e3:ek" & y1 - 1)
    r1.Borders.LineStyle = xlContinuous

    's1.Protect ("abc")

End Sub

Diese Routine läuft folgendermaßen ab:

Es wird in Zelle c3 angefangen und die Schleife wird so lange zeilenweise nach unten weiter durchlaufen, bis die erste leere Zelle gefunden wird. Du mußt also nur die Formel in c4 (nimmt jeweils das Datum der darüber liegenden Zelle und addiert einen Tag) so weit nach unten ziehen, wie Du möchtest.

Es wird dann in der Variablen b1 festgestellt, welchen "Zustand" die aktuelle Zeile bekommen soll. Das kann also "Werktag" (0), "Schulferien" (1), "Samstag" (2), "Sonntag" (3) oder "Feiertag" (4) sein. Einer Erweiterung, z.B. "Betriebsruhe" (5), steht damit nichts im Wege.

Eine kleine Variation ist noch bei "Feiertag" möglich (und wahrscheinlich auch sinnvoll): Z.Z. wird der Feiertag nur gesetzt, wenn es nicht eh schon "Schulferien", "Samstag" oder "Sonntag" ist. Ich glaube:

Code:

        If (f1 <> "") And (b1 <> 1) Then b1 = 4 'feiertag

macht mehr Sinn, damit Feiertage auch in den Ferien und am Wochenende als solche gekennzeichnet werden.

Abhängig von b1 wird dann festgelegt, ob die Zellen rechts (E:EK) gesperrt werden, ob ggf. vorhandene Inhalte gelöscht werden und welche Farbe die Zellen bekommen sollen.

Zu guter Letzt bekommt der durchlaufene Bereich auch noch ein Gitternetz verpaßt, damit die Spalten leichter voneinander zu unterscheiden sind.

Die Zeilen

Code:

s1.Cells.Interior.Color = xlColorIndexNone
s1.Cells.Borders.LineStyle = xlNone

am Anfang sollten dann, wenn es funktioniert, mit ' auskommentiert oder gelöscht werden. Die würden Dir sonst Farben und Linien auch außerhalb des bearbeiteten Bereichs löschen.

Außerdem müßte natürlich, damit der Schutz überhaupt wirklich wirksam wird. die Zeile

Code:

    's1.Protect ("abc")

am Ende durch Entfernen des ' aktiviert werden. In dieser und der dazu gehörigen ".Protect" Zeile kannst Du dann ggf. auch das Passwort anpassen. Dabei kannst Du übrigens auch gerne "" (also ein leeres Passwort) benutzen. Hauptsache, das Blatt ist "geschützt" und das Sperren der Zellen wird dadurch wirksam.

Gruß, Michael