Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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" wrote: 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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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 "iperlovsky" wrote: 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" wrote: 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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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 On Tue, 4 Aug 2009 08:57:01 -0700, iperlovsky wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
Sorry, still nothing...
"Jacob Skaria" wrote: 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 "iperlovsky" wrote: 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" wrote: 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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
--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 --------------- Jacob Skaria "iperlovsky" wrote: 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" wrote: 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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time stamp function trouble shooting
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. "Jacob Skaria" wrote: --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 --------------- Jacob Skaria "iperlovsky" wrote: 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" wrote: 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 --------------- Jacob Skaria "iperlovsky" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble shooting Edit/Replace with in a formula | Excel Discussion (Misc queries) | |||
Trouble shooting Excel? | Excel Discussion (Misc queries) | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
trouble shooting section | Excel Discussion (Misc queries) | |||
Excel trouble shooting - no gridlines | Excel Discussion (Misc queries) |