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

danbates

Active Member
Joined
Oct 8, 2017
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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

Active Member
Joined
Oct 8, 2017
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Dan

Try this instead (on a copy)..

VBA Code:
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
 
Solution

danbates

Active Member
Joined
Oct 8, 2017
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
Perfect.
So the full stop before the range was the problem?
Thank you for your help.
Dan
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,487
Members
416,031
Latest member
Omega67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top