Working with the now function.

  • Thread starter Thread starter NDBC
  • Start date Start date
N

NDBC

I am using the following code to put the current time in column c when I type
a riders number into column a. What I would like to be able to do is to
change the code so that if there is already a value in column a, I can change
it (like in the case of a typo that needs fixing) I can without the time
getting updated again.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Format(Now, "h:mm:ss")
End If
End Sub

Thanks again.
 
Try the below code..Additional code has been added to SelectionChange event
and a general variable has been declared...


Dim varTemp As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
If varTemp = "" Then Target.Offset(0, 1).Value = Format(Now, "h:mm:ss")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
varTemp = Target.Text
End If
End Sub


If this post helps click Yes
 
Another way..I hope this is the simpler one

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Format(Now, "h:mm:ss")
End If
End If
End Sub


If this post helps click Yes
 
Jacob,

Thanks, works great. I didn't try the first way but I'm sure it would of
worked as well.
 
Jacob,

Just tried the first way. It diodn't work for some reason. Just thought I'd
let you know.
 
I have tested it now. That should work if you place the declaration on top
(outside any event)

Dim varTemp As Variant

If this post helps click Yes
 
I don't know if this adds any value to what Jacob Skaria has already
provided, but I got to wondering "what if they delete the entry in column B -
does NDBC want to clear out the date automatically also?" and if you would
want that, I modified his second code offering like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
'did the user [Del] contents of cell in B?
If IsEmpty(Target) Then
'yes they did, erase the date also
Target.Offset(0, 1).ClearContents
ElseIf Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Format(Now, "h:mm:ss")
End If
End If
End Sub

if the user simply edits an existing entry in B, the original time remains.
But if they delete the contents of B# then the time is deleted and when they
make a new entry in B#, the new time will be entered into C#.
 
Back
Top