VBA Protection error

[Colleen45]

Hi There
I have a worksheet that i would like to protect because of complex formulas
in this work sheet i have some VBA Code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Or Target.Column = 18 Then
If Target.Value > 0 Then
Target.Offset(0, 2).Value = Date
Else
Target.Offset(0, 2).Value = ""
End If
End If
End Sub

And it works perfectly well
However when i protect the worksheet it produces an error as follows:

Runtime Error '1004'
Application Defined or Object-defined error

And yet as soon as the sheet protection is removed, it all works fine again.
How do i get around this.

Regards

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 1 Or Target.Column = 18 Then
        Target.Parent.Protect UserInterfaceOnly:=True
        If Target.Value > 0 Then
            Target.Offset(0, 2).Value = Date
        Else
            Target.Offset(0, 2).Value = ""
        End If
    End If
End Sub

 

[hsk]

Not sure ..... if ur sheet is protected ..... how Worksheet_Change will be triggered ?

But u can try inserting code to unprotect the sheet at the start of macro,
something like

Sub macro()
ActiveSheet.Unprotect
.....
.........ur logic


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End sub

 

[Colleen45]

Thank you so very much it worked perfectly
God Bless