run-time error 424; object required error

[dsheard2015]

Hello,

I am receiving a run-time error 424; object required message. The error appeared after I added the code lines in red.

Basically, whenever the worksheet opens, I'm trying to lock any cell within that range that is not blank. Any help is greatly appreciated!

Thanks,
Dave

Private Sub Worksheet_Activate()


Dim rLockable As Range
Dim cl As Range
    
ActiveSheet.Unprotect Password:=Sheets("Worksheet Names").Range("O11").Value


    If Range("H1").Value = Sheets("User List").Range("A56").Value Then


        Range("A8:A53, A64:A109, A120:A165, A176:A221, A232:A277, A288:A333, A344:A389, A400:A445, A456:A501, A512:A557, C8:C53, C64:C109, C120:C165, C176:C221, C232:C277, C288:C333, C344:C389, C400:C445, C456:C501, C512:C557").Locked = True
        Range("B8:B53, B64:B109, B120:B165, B176:B221, B232:B277, B288:B333, B344:B389, B400:B445, B456:B501, B512:B557").Locked = False


        Else


            If Range("H1") = "" Then


                Range("A8:A53, A64:A109, A120:A165, A176:A221, A232:A277, A288:A333, A344:A389, A400:A445, A456:A501, A512:A557, C8:C53, C64:C109, C120:C165, C176:C221, C232:C277, C288:C333, C344:C389, C400:C445, C456:C501, C512:C557").Locked = False
                Range("B8:B53, B64:B109, B120:B165, B176:B221, B232:B277, B288:B333, B344:B389, B400:B445, B456:B501, B512:B557").Locked = True


            End If
    End If


[COLOR=#ff0000]Set rLockable = Range("A8:C53, A64:C109, A120:C165, A176:C221, A232:C277, A288:C333, A344:C389, A400:C445, A456:C501, A512:C557")[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]    For Each cl In Target[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]        If cl.Value = "" Then[/COLOR]
[COLOR=#ff0000]            cl.Locked = False[/COLOR]
[COLOR=#ff0000]            [/COLOR]
[COLOR=#ff0000]            Else[/COLOR]
[COLOR=#ff0000]            [/COLOR]
[COLOR=#ff0000]            cl.Locked = True[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]    Next cl[/COLOR]


ActiveSheet.Protect Password:=Sheets("Worksheet Names").Range("O11").Value


End Sub




Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rLockable As Range
    Dim cl As Range
    
    Set rLockable = Range("A8:C53, A64:C109, A120:C165, A176:C221, A232:C277, A288:C333, A344:C389, A400:C445, A456:C501, A512:C557")
    
    'If target is within the range then do nothing
    If Intersect(rLockable, Target) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:=Sheets("Worksheet Names").Range("O11").Value
    For Each cl In Target
        If cl.Value <> "" Then
            check = MsgBox("Is this entry correct? This cell cannot be changed after entering a value.", vbYesNo, "Cell Lock Notification")
            If check = vbYes Then
                cl.Locked = True
            Else
                ActiveCell.Select
            End If
        End If
    Next cl
ActiveSheet.Protect Password:=Sheets("Worksheet Names").Range("O11").Value
End Sub

 

[MARK858]

You don't appear to have a Target in that sub...

For Each cl In Target

did you mean

For Each cl In rLockable

 

[dsheard2015]

Hello Mark858,

I am away from my computer right now but I will make that change once I get back to the office and let you know.

Thank you!

Dave

 

[dsheard2015]

MARK858,

I made the change you suggested and it works great! Thanks for taking the time to respond!

Dave