Can I make time stamp function that "sticks"?

  • Thread starter Thread starter plh
  • Start date Start date
P

plh

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!
 
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.
 
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

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
Click to expand...

I keep hitting "Esc", but I'm still here!
 
Back
Top