I
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
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