Unable to Lock Cells when combined with an other intruction

[Bwanna]

Hi,

I have working code to Lock/Unlock a range of cells based on a DropDown selection ("PASSTHRU") of another cell. When I ADD a line to BLANK each cell (using ="") prior to Locking, I get the error: "Run-time error 1004 Unable to set locked property of the range class"

Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub


' Lock/Unlock and Clear Cells when PASSTHRU Selected from Dropdown
    If Not Intersect(Target, Range("I6:I14")) Is Nothing And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
            Dim i As Integer
            If Target.Value = "PASSTHRU" Then
                For i = 1 To 5
[B]                    Target.Offset(0, i) = ""[/B]
                    Target.Offset(0, i).Locked = True
                Next i
            Else
                For i = 1 To 5
                    Target.Offset(0, i).Locked = False
                Next i
            End If
        ActiveSheet.Protect
    End If
End Sub

 

[Bwanna]

SOLVED:

I realized forgot to add Application.EnableEvents = False and Application.EnableEvents = True Statements.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub


' Lock/Unlock and Clear Cells when PASSTHRU Selected from Dropdown
    If Not Intersect(Target, Range("I6:I14")) Is Nothing And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
            Dim i As Integer
            If Target.Value = "PASSTHRU" Then
Application.EnableEvents = False
                For i = 1 To 5
                    Target.Offset(0, i) = ""
                    Target.Offset(0, i).Locked = True
                Next i
            Else
                For i = 1 To 5
                    Target.Offset(0, i).Locked = False
                Next i
Application.EnableEvents = True
            End If
        ActiveSheet.Protect
    End If
End Sub