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
---------------
Jacob Skaria


"NDBC" wrote:

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.

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 Skaria


"NDBC" wrote:

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.

Jacob,

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

"Jacob Skaria" wrote:

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 Skaria


"NDBC" wrote:

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.

Jacob,

Just tried the first way. It diodn't work for some reason. Just thought I'd
let you know.


"NDBC" wrote:

Jacob,

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

"Jacob Skaria" wrote:

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 Skaria


"NDBC" wrote:

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.

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
---------------
Jacob Skaria


"NDBC" wrote:

Jacob,

Just tried the first way. It diodn't work for some reason. Just thought I'd
let you know.


"NDBC" wrote:

Jacob,

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

"Jacob Skaria" wrote:

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 Skaria


"NDBC" wrote:

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.

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

"NDBC" wrote:

Jacob,

Just tried the first way. It diodn't work for some reason. Just thought I'd
let you know.


"NDBC" wrote:

Jacob,

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

"Jacob Skaria" wrote:

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 Skaria


"NDBC" wrote:

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.