VBA - Problem with Locked property

[WinteE]

Hi,

I have the following code that should do the following :

When a value is entered in the column 'Stock' that is greater then zero it should unlock the four cells right of the target cell. This part does what it should do.
Otherwise, when the value is less or equal to zero then it should clear the contents of the target cell and of all four cells right from it, and also lock those cells at the right.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
i = 0

    ActiveSheet.Unprotect
    
    If Not Intersect(Target, Range("Stock")) Is Nothing Then
        If Target.Value > 0 Then
            For i = 1 To 4
                With Target.Offset(0, i)
                    .Locked = False
                End With
            Next i
        Else
            For i = 1 To 4
                With Target.Offset(0, i)
                    .ClearContents
                    .Locked = True
                End With
            Next i
        End If
    End If
    
    ActiveSheet.Protect

End Sub

I am getting a 'Runtime error 1004' message with "Unable toe set the Locked property of the Range class" when I enter a value less or equal to zero.

 

[WinteE]

The problem is solved.

The error was caused by the ClearContents command. This fires the Change event.

I've added some lines to disable the change event when the code is running.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
i = 0

    ActiveSheet.Unprotect
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("Stock")) Is Nothing Then
        For i = 1 To 4
            If Target.Value > 0 Then
                With Target.Offset(0, i)
                    .Locked = False
                End With
            Else
                With Target.Offset(0, i)
                    .ClearContents
                    .Locked = True
                End With
            End If
        Next i
    End If
    
    Application.EnableEvents = True
    ActiveSheet.Protect

End Sub