Lock Specific Cells if Text entered in another

[NeilBarn1701]

Hi, I hope someone can help me with this issue.

I need to create a timesheet for staff to complete (see attached)
In cell AI19 where at present the word 'Manager' sits the line manager will change this word to his initials.
After this is entered I would like the cells that correspond to that week e.g. A4:AC17 to lock so they can't be adjusted by the user.

This is then repeated for the next week and so on.

I have cleared the worksheet of all protected cells and added the following VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("AI19") = "Manager" Then
Range("A4:AC17").Locked = False
ElseIf Range("AI19") = "NB" Then
Range("A4:AC17").Locked = True
End If
End Sub

However, when I protect the sheet and try and enter data with or without the cell AI19 having the word 'Manager' I get a VBA error Run Time error '1004' Unable to set the locked property of the Range class.

I have checked, their are merged cells but not in the range given any help would be much appreciated.

Thanks
Neil

 

[FormR]

Hi, you need to unprotect the sheet before setting the locked property and then protect it again afterwards - you might also consider only having the code run when the cell you are testing is changed.

Something like..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AI19")) Is Nothing Then
    Me.Unprotect
    If Range("AI19") = "Manager" Then
        Range("A4:AC17").Locked = False
    ElseIf Range("AI19") = "NB" Then
        Range("A4:AC17").Locked = True
    End If
    Me.Protect
End If
End Sub

 

[NeilBarn1701]

Hi, you need to unprotect the sheet before setting the locked property and then protect it again afterwards - you might also consider only having the code run when the cell you are testing is changed.

Something like..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AI19")) Is Nothing Then
    Me.Unprotect
    If Range("AI19") = "Manager" Then
        Range("A4:AC17").Locked = False
    ElseIf Range("AI19") = "NB" Then
        Range("A4:AC17").Locked = True
    End If
    Me.Protect
End If
End Sub

Thank you for your assistance, much appreciated. Since the manager will need to lock each week after it is completed; would I duplicate each If statement with the new range each time. I need to prepare this for 2023 so will need to have the VBA ready for each of the 52 weeks next year.

 

[FormR]

Hi, I don't really understand the follow up question to be honest - but if you want the range that is being locked to be dynamic based on the current week or year (or something else) then if you are able to explain the logic I'm sure someone could help write the VBA.

Note, it would probably be best if you created a new thread for the new question.