Macro stops working when sheet is protected

[RefLib1978]

Hi –
I hope that this question isn’t a duplicate. I tried searching all over with
no success so I thought that I would try posting.
Basically,
I have created a spreadsheet template that will be used to track purchases.
I found a macro that automatically enters the date into columns A and H
whenever data is entered into B and I respectively. The code is in General –
Declarations:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If
If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

I would now like to lock and protect columns A and H (as well as a few other
select cells). Whenever I try to protect the sheet no matter what has been
locked, the date stops being automatically entered. An error message does not
ever pop up and it never asks if I would like to debug it. The date just does
not appear.

I do not necessarily have to use a macro. I have just found that this
particular macro works the best. In the past I have gotten errors and or the
date would automatically update every time I opened the file. I just want
some way to track the date I ordered something and the date the order was
received. Does anyone know a way I can keep the macro and just protect the
cells? If that isn’t possible is there a good function that I could use in
the stead of the code? I’d like to be able to protect the cells because I
won’t be the only one using it. Thanks for any help or guidance you can offer.

 

[Jim Thomlinson]

Unprotect and reprotect the sheet...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
me.unprotect
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If
If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If

ws_exit:
me.protect
Application.EnableEvents = True
End Sub

 

[Jim Thomlinson]

Unprotect and reprotect the sheet...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
me.unprotect
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If
If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then
With Target
If .Value <> "" Then
..Offset(0, -1).Value = Format(Date, "dd/mm/yyyy")
End If
End With
End If

ws_exit:
me.protect
Application.EnableEvents = True
End Sub

 

[RefLib1978]

Thank you! That worked perfectly. Not to regress too much into my
adolescence, but you totally rule!

 

[RefLib1978]

Thank you! That worked perfectly. Not to regress too much into my
adolescence, but you totally rule!

 

[Jim Thomlinson]

Actually my wife rules, but when she is not around I pretend like I do... ;-)

 

[Jim Thomlinson]

Actually my wife rules, but when she is not around I pretend like I do... ;-)