VBA Excel. Delete time stamp in protected cells using event code.

[Jmuniz_Vargas]

Good morning to all,

I wish to delete values in editable rows in a protected excel sheet. I have the following code event
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="avalon"
If Target.Column = 2 Then
Application.EnableEvents = False
Cells(Target.Row, 5).Value = Date + Time
Application.EnableEvents = True
End If
ActiveSheet.Protect Password:="avalon"
End Sub

The idea is when I delete data in editable rows located in column 'B' at the same time, in column 'E'(where is located an stamp time and, also, they are protected) to delete the related cell to clean up the data from those columns ('B'and 'E'). I hope to be clear, if not let me know to try to clarify my need to discover the proper code based on events.

Thanks in advance.

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="avalon"
    If Target.Column = 2 Then
    Application.EnableEvents = False
        If Len(Target.Value) = 0 Then
            Range("E" & Target.Row).Value = ""
        Else
            Cells(Target.Row, 5).Value = Date + Time
        End If
    Application.EnableEvents = True
    End If
ActiveSheet.Protect Password:="avalon"
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="avalon"
If Target.Column = 2 Then
Application.EnableEvents = False
If Target.Value = "" Then
Target.Offset(0, 3).Value = ""
Else
Target.Offset(0, 3).Value = Date + Time
End If
Application.EnableEvents = True
End If
ActiveSheet.Protect Password:="avalon"
End Sub

 

[My Aswer Is This]

As a side note:
You do not need to use:

Target.Offset(0, 3).Value = Date + Time

You could use

Target.Offset(0, 3).Value = Now

 

[Jmuniz_Vargas]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="avalon"
    If Target.Column = 2 Then
    Application.EnableEvents = False
        If Len(Target.Value) = 0 Then
            Range("E" & Target.Row).Value = ""
        Else
            Cells(Target.Row, 5).Value = Date + Time
        End If
    Application.EnableEvents = True
    End If
ActiveSheet.Protect Password:="avalon"
End Sub

Thank you very much. It is working perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback