Time stamp function trouble shooting

[iperlovsky]

I have the following function code that I wrote into a sheet that is supposed
to time stamp when a user selects "Y" from a drop down menu in a nearby cell.
The issue is that from time to time, for reasons I do not know, the function
ceases to work. What happens is that a run-time error pops up while a user
is dragging rows down in another par of the sheet, or something akin to this,
and then the sheet remains unprotected with the function disabled. Then I
can no longer get the function to activate again. Any suggestions on how to
improve upon this code?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

With Worksheets("Administrator")
.Unprotect Password:="dist"
End With

If (Target.Row = 13 And Target.Column = 3 And Target = "Y") Then
ActiveSheet.Cells(16, 3) = Format(Now(), "mmm dd, yyyy h:mm AMPM;@")
End If

With Worksheets("Administrator")
.Protect Password:="dist"
End With

Application.EnableEvents = True

End Sub

 

[Jacob Skaria]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C13")) Is Nothing Then
If Target.Count = 1 And UCase(Target.Text) = "Y" Then
Me.Unprotect Password:="dist"
Range("C16") = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
Me.Protect Password:="dist"
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[iperlovsky]

I am not sure why but this does not work. One other note: "C13" is a drop
down with either "N" or "Y" as available selections; although the user could
delete the contents because the cell is unprotected.

 

[Jacob Skaria]

Try the below...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C13")) Is Nothing Then
If Target.Count = 1 And UCase(Trim(Target.Text)) = "Y" Then
Me.Unprotect Password:="dist"
Range("C16") = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
Me.Protect Password:="dist"
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Gord Dibben]

Error trap to reset on errors.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall '<<<<<<<<<< added line
Application.EnableEvents = False

With Worksheets("Administrator")
.Unprotect Password:="dist"
End With

If (Target.Row = 13 And Target.Column = 3 And Target = "Y") Then
ActiveSheet.Cells(16, 3) = Format(Now(), "mmm dd, yyyy h:mm AMPM;@")
End If

enditall: '<<<<<<<<<<<<<
With Worksheets("Administrator")
.Protect Password:="dist"
End With

Application.EnableEvents = True

End Sub

 

[iperlovsky]

Sorry, still nothing...

Try the below...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C13")) Is Nothing Then
If Target.Count = 1 And UCase(Trim(Target.Text)) = "Y" Then
Me.Unprotect Password:="dist"
Range("C16") = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
Me.Protect Password:="dist"
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Jacob Skaria]

--Restart excel and try
--The reason why it gets stuck in between is becasuse ; by the time the code
gives and error Application.EnableEvents = False line is already
executed...and so the events from there on are not triggered.....

If this post helps click Yes

 

[iperlovsky]

I rebooted my computer and it works. The run-time error must cause a
'glitch' in my user script which can only be cleared if I start up a new
version of excel.