Open workbook on sheet with todays date and then unlock certain cells.

[danbates]

Hi,

I have a code that finds the correct sheet depending on the date when the file is opened.

What I would like is once the sheet is found, the code then unlocks certain cells depending on a cells value.

I have the following code but I can't get it to unlock the cells I want. I keep getting the following error message:
Run time error 1004 - Unable to set the locked property of the range class.

Sub aaa()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim DAYS As Range
    Dim NIGHTS As Range

    Set DAYS = Range("C5", "L5")
    Set NIGHTS = Range("C15", "L15")

    Flag = False
    For Each ws In Sheets
        If ws.Name = Format(Date, "dd-mmm-yy") And Time > TimeValue("05:40:00") And Time < TimeValue("23:59:59") Then
                ws.Select
                Flag = True
                                
                With ws
                .UnProtect Password:=""
                If Range("L5").Value = "" Then
                DAYS.Locked = False
                .Protect Password:=""
                ElseIf Range("L5").Value <> "" Then
                DAYS.Locked = True
                .Protect Password:=""
                End If
                End With
                    
                ElseIf ws.Name = Format(Date - 1, "dd-mmm-yy") And Time > TimeValue("00:00:00") And Time < TimeValue("05:39:59") Then
                ws.Select
                Flag = True
                
                With ws
                .UnProtect Password:=""
                If Range("L15").Value = "" Then
                DAYS.Locked = False
                ElseIf Range("L15").Value <> "" Then
                DAYS.Locked = True
                .UnProtect Password:=""
                End If
                End With
           End If
    Next ws
If Flag = False Then MsgBox "No Sheet Found." & vbNewLine & vbNewLine & "Please check the date."
        Application.ScreenUpdating = True
End Sub

I've looked the error up and it's something to do with the amount of sheets I have in my workbook but I don't know where to go from here to sort it.

Any help sorting this issue would be appreciated.

Thanks

Dan

 

[sykes]

What if you set the DAYS & NIGHTS ranges within the worksheet; just wondering whether Excel doesn't currently know WHICH C15 / L15 you're referring to...
NB UNTESTED, so always try on a COPY of your work, first!

Sub aaa()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim DAYS As Range
    Dim NIGHTS As Range

    Flag = False
    For Each ws In Sheets

    Set DAYS = .Range("C5", "L5")
    Set NIGHTS = .Range("C15", "L15")
        If ws.Name = Format(Date, "dd-mmm-yy") And Time > TimeValue("05:40:00") And Time < TimeValue("23:59:59") Then
                ws.Select
                Flag = True
                               
                With ws
                .UnProtect Password:=""
                If Range("L5").Value = "" Then
......

 

[danbates]

Hi,
Thank you for you reply.
I've just tried your suggestion but unfortunately it comes up with an compile error - Invalid or unqualified reference.

I've managed to get the following code working but I have to literally select each cell and then tell it to do what I want, which isn't good because there will be a lot more cells to lock/unlock later.

If Range("L5") = "" Then
ActiveSheet.UnProtect Password:=""
Range("C5").Select
Selection.Locked = False
Range("L5").Select
Selection.Locked = False
ActiveSheet.Protect Password:=""
                
ElseIf Range("L5") <> "" Then
ActiveSheet.UnProtect Password:=""
Range("C5").Select
Selection.Locked = True
Range("L5").Select
Selection.Locked = True
ActiveSheet.Protect Password:=""

thanks
Dan

 

[sykes]

Dan

Try this instead (on a copy)..

Sub aaa()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim DAYS As Range
    Dim NIGHTS As Range



    Flag = False
    For Each ws In Sheets
   
   
        Set DAYS = ws.Range("C5", "L5")
        Set NIGHTS = ws.Range("C15", "L15")
   
        If ws.Name = Format(Date, "dd-mmm-yy") And Time > TimeValue("05:40:00") And Time < TimeValue("23:59:59") Then
                ws.Select
                Flag = True
                               
                With ws
                    .Unprotect Password:=""
                    If .Range("L5").Value = "" Then
                        DAYS.Locked = False
                        .Protect Password:=""
                        ElseIf .Range("L5").Value <> "" Then
                        DAYS.Locked = True
                        .Protect Password:=""
                    End If
                End With
                   
                ElseIf ws.Name = Format(Date - 1, "dd-mmm-yy") And Time > TimeValue("00:00:00") And Time < TimeValue("05:39:59") Then
                ws.Select
                Flag = True
               
                With ws
                    .Unprotect Password:=""
                    If .Range("L15").Value = "" Then
                        DAYS.Locked = False
                        ElseIf .Range("L15").Value <> "" Then
                        DAYS.Locked = True
                        .Unprotect Password:=""
                    End If
                End With
           End If
    Next ws
If Flag = False Then MsgBox "No Sheet Found." & vbNewLine & vbNewLine & "Please check the date."
        Application.ScreenUpdating = True
End Sub

 

[danbates]

Perfect.
So the full stop before the range was the problem?
Thank you for your help.
Dan

 

[sykes]

Quite.
I'd had a quick squidge over your code, and thought we were working within a "With" statement at the time, but it wasn't the case.

Glad it helped, and thanks for the feedback.