Error on lock cells

[Beatrice]

Hi everyone, I need some help to fix below VBA code....

- it seems working prefectly fine with only "Service 1", but when I added the duplicate for "Service 2", there is an error but I have no clue why.

here I attached the file download link from wetransfer: unsolve.xlsm

===
Private Sub Worksheet_change(ByVal Target As Range)

ActiveSheet.Unprotect Password:="123PASS"
'SERVICE 1
If Range("C3") = "Yes" Then
Range("E3").Locked = False
If Range("E3") = "Yes" Then
Range("F3,G3").Locked = False
Else
Range("F3,G3").Locked = True
End If

Else
Range("E3,F3,G3").Locked = True
End If

If Not Intersect(Target, Range("E3")) Is Nothing Then
Range("F3,G3").ClearContents
Else
If Not Intersect(Target, Range("C3")) Is Nothing Then
Range("E3").ClearContents
End If
End If

'SERVICE 2
If Range("C4") = "Yes" Then
Range("E4").Locked = False
If Range("E4") = "Yes" Then
Range("F4,G4").Locked = False
Else
Range("F4,G4").Locked = True
End If

Else
Range("E4,F4,G4").Locked = True
End If

If Not Intersect(Target, Range("E4")) Is Nothing Then
Range("F4,G4").ClearContents
Else
If Not Intersect(Target, Range("C4")) Is Nothing Then
Range("E4").ClearContents
End If
End If


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="123PASS"
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub

===

Many thanks in advance for your help.

Beatrice

 

[FatBoyClam]

When you do the ClearContents command, you are triggering the Worksheet_Change event a second time, before completing the run you are already on. This will happen recursively, until one iteration works it's way to the end, at which point it protects the sheet, and then passes back to the previous iteration, which then tries to change the Locked state of the cells, but can't because the sheet is protected.

Enter this line at the start of your Sub

Application.EnableEvents = False

and this at the end

Application.EnableEvents = true

When EnableEvents is false these Worksheet events (and the Workbook ones) will not fire, so the Change event you're using does not result in calling itself.

You need to be a little wary, as if your code triggers another error, you could leave the EnableEvents property set to false, so it won't react to any more changes. You'd be best to look at handling errors, and ensuring you set the EnableEvents property back to true in the error handler.