Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I make time stamp function that "sticks"?
Hello all,
When a user enters data in (say) B:6, I need to have the date of the entry appear in B:5, and the Time in D:5. The following does it OK in one respect: When the user deletes the data, that date (& time) go away. They re-appear when new data is added. BUT.... it keeps regenerating so that the times & dates of existing entries all end up the same when the user enters new data, reopens the sheet, or does anything else that causes the sheet to refresh. Here is the code: (Date function only: the Time function is similar) Function DateStamp(varTheCell) If varTheCell.Value < "" Then DateStamp = Date Else DateStamp = "" End If End Function I mean, I can see why the regens happen. I tried to rewrite it to hold off the update based on what appears in the cell with the DateStamp function in it, but Excel (97) complained of a circular reference. Is there some way out of my dilemma? Thanking everyone in advance, -plh I keep hitting "Esc", but I'm still here! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I make time stamp function that "sticks"?
You need worksheet events.
I am not sure of the rule for where to store the date, but lets assume it the row above for date, row above, 2 cols right for time, this code does it. Firstly, create a named range for all the cells that will generate a date and time, I have used a range called 'changes'. Then right-click the sheet tab, select View Code from the menu, and paste this code into the code pane shown Private Sub Worksheet_Change(ByVal Target As Range) Const iDateRowOff As Long = -1 Const iDateColOff As Long = 0 Const iTimeeRowOff As Long = -1 Const iTimeColOff As Long = 2 Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("changes")) Is Nothing Then If Target.Count = 1 Then If IsEmpty(Target.Value) Then Target.Offset(iDateRowOff, iDateColOff).Value = "" Target.Offset(iTimeeRowOff, iTimeColOff).Value = "" Else Target.Offset(iDateRowOff, iDateColOff).Value = Format(Date, "dd mmm yyyy") Target.Offset(iTimeeRowOff, iTimeColOff).Value = Format(Now, "hh:mm:ss") End If End If End If ws_exit: Application.EnableEvents = True End Sub If the offset rule is different, just change those constant values to suit. -- HTH Bob Phillips "plh" wrote in message ... Hello all, When a user enters data in (say) B:6, I need to have the date of the entry appear in B:5, and the Time in D:5. The following does it OK in one respect: When the user deletes the data, that date (& time) go away. They re-appear when new data is added. BUT.... it keeps regenerating so that the times & dates of existing entries all end up the same when the user enters new data, reopens the sheet, or does anything else that causes the sheet to refresh. Here is the code: (Date function only: the Time function is similar) Function DateStamp(varTheCell) If varTheCell.Value < "" Then DateStamp = Date Else DateStamp = "" End If End Function I mean, I can see why the regens happen. I tried to rewrite it to hold off the update based on what appears in the cell with the DateStamp function in it, but Excel (97) complained of a circular reference. Is there some way out of my dilemma? Thanking everyone in advance, -plh I keep hitting "Esc", but I'm still here! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I make time stamp function that "sticks"?
I tried it & so far it is working splendidly! I had to split it into another Sub
& call that twice because I have two non-contiguous ranges to check, but that worked out also. Thank You , -plh In article , "Bob says... You need worksheet events. I am not sure of the rule for where to store the date, but lets assume it the row above for date, row above, 2 cols right for time, this code does it. Firstly, create a named range for all the cells that will generate a date and time, I have used a range called 'changes'. Then right-click the sheet tab, select View Code from the menu, and paste this code into the code pane shown Private Sub Worksheet_Change(ByVal Target As Range) Const iDateRowOff As Long = -1 Const iDateColOff As Long = 0 Const iTimeeRowOff As Long = -1 Const iTimeColOff As Long = 2 Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("changes")) Is Nothing Then If Target.Count = 1 Then If IsEmpty(Target.Value) Then Target.Offset(iDateRowOff, iDateColOff).Value = "" Target.Offset(iTimeeRowOff, iTimeColOff).Value = "" Else Target.Offset(iDateRowOff, iDateColOff).Value = Format(Date, "dd mmm yyyy") Target.Offset(iTimeeRowOff, iTimeColOff).Value = Format(Now, "hh:mm:ss") End If End If End If ws_exit: Application.EnableEvents = True End Sub If the offset rule is different, just change those constant values to suit. -- HTH Bob Phillips "plh" wrote in message ... Hello all, When a user enters data in (say) B:6, I need to have the date of the entry appear in B:5, and the Time in D:5. The following does it OK in one respect: When the user deletes the data, that date (& time) go away. They re-appear when new data is added. BUT.... it keeps regenerating so that the times & dates of existing entries all end up the same when the user enters new data, reopens the sheet, or does anything else that causes the sheet to refresh. Here is the code: (Date function only: the Time function is similar) Function DateStamp(varTheCell) If varTheCell.Value < "" Then DateStamp = Date Else DateStamp = "" End If End Function I mean, I can see why the regens happen. I tried to rewrite it to hold off the update based on what appears in the cell with the DateStamp function in it, but Excel (97) complained of a circular reference. Is there some way out of my dilemma? Thanking everyone in advance, -plh I keep hitting "Esc", but I'm still here! I keep hitting "Esc", but I'm still here! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make the INFO("directory") function work? | Excel Worksheet Functions | |||
Can you make a cell = 0 if original function is creating a"#ref!"? | Excel Worksheet Functions | |||
Make "Edit" mode default, rather than "Enter"? | Excel Discussion (Misc queries) | |||
can i make a "repeat until" kind of function in excel2003 | Excel Discussion (Misc queries) | |||
how can I make an excel cell "mark" or "unmark" when clicked on? | Excel Discussion (Misc queries) |