Macro stops working when sheet is protected
 

Hi €“
I hope that this question isnt 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 isnt possible is there a good function that I could use in
the stead of the code? Id like to be able to protect the cells because I
wont be the only one using it. Thanks for any help or guidance you can offer.

Macro stops working when sheet is protected
 

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

--
HTH...

Jim Thomlinson


"RefLib1978" wrote:

Hi €“
I hope that this question isnt 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 isnt possible is there a good function that I could use in
the stead of the code? Id like to be able to protect the cells because I
wont be the only one using it. Thanks for any help or guidance you can offer.

Macro stops working when sheet is protected
 

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

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"RefLib1978" wrote:

Hi €“
I hope that this question isnt 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 isnt possible is there a good function that I could use in
the stead of the code? Id like to be able to protect the cells because I
wont be the only one using it. Thanks for any help or guidance you can offer.

Macro stops working when sheet is protected
 

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

Jim Thomlinson


"RefLib1978" wrote:

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

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"RefLib1978" wrote:

Hi €“
I hope that this question isnt 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 isnt possible is there a good function that I could use in
the stead of the code? Id like to be able to protect the cells because I
wont be the only one using it. Thanks for any help or guidance you can offer.