time stamp based on action in column.

[J.W. Aldridge]

Hi.
I need to place a date stamp on the row in column A, that is triggered
by any action on a row.
I need to place a time stamp on the row in column B, that is triggered
by any action on a row in column C.

1/10/2007 13:54

I have the code for the first one, but need to trigger the time based
on action in C for the second.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A" & Target.Row) = Date
End Sub

Thanx

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error Goto ws_exit

If Not Intersect(Target, Me.Colums(3)) Is Nothing Then
Me.Range("B", Target.Row).Value = Time
End If
Me.Range("A" & Target.Row).Value = Date

ws_exit:
Application.EnableEvents = True
On Error Goto 0
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Don Guillett]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Range("A" & Target.Row) = Date & " " & Time

'or
'Range("A" & Target.Row) = Format(Now(), "mm/yy/dd h:mm")
End Sub

 

[J.W. Aldridge]

Thanx Bob, but date stamp is triggered by column D and the time stamp
is not triggering at all.

If i could get both date (column A) and time (column B) triggered by
the action in the same column (column E) that would be fine as well.

 

[Don Guillett]

At first you say col C, then D, then E. It would be helpful if you decided
which or if any of the three. Also, do you want the date and time in ONE
column or date in one column and time in another?

 

[Bob Phillips]

You said any action. However..

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error Goto ws_exit

If Not Intersect(Target, Me.Colums(3)) Is Nothing Then
Me.Range("B", Target.Row).Value = Time
ElseIf Not Intersect(Target, Me.Colums(4)) Is Nothing Then
Me.Range("A" & Target.Row).Value = Date
End If

ws_exit:
Application.EnableEvents = True
On Error Goto 0
End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[J.W. Aldridge]

Bob, I tried that one, and the date stamp works fine but, the time
stamp in column B still does not trigger.

Don,
I made some changes to my layout, wasnt trying to complicate things,
would've just changed it to fit the new column. I am good enough to
alter a formula that works but, not quite at the level to write the
formula yet.

The date stamp worked on the formula above. The time stamp didnt for
some reason.

I want the date to populate in column A if any action on the row is
done.
I want the time stamp in (separate) column B, if any action takes place
in column E.

Hope this is clear....

Thanx.

 

[Bob Phillips]

Now I am really confused. Originally, you said any action on a row triggers
the date stamp, then you said the date stamp is triggered by column D, now
you are saying any action again.

Whatever it should be, I had an error. So assuming C & D

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit

If Not Intersect(Target, Me.Columns(3)) Is Nothing Then
Me.Range("B" & Target.Row).Value = Time
ElseIf Not Intersect(Target, Me.Columns(4)) Is Nothing Then
Me.Range("A" & Target.Row).Value = Date
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub

If C and any cell then

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit

If Not Intersect(Target, Me.Columns(3)) Is Nothing Then
Me.Range("B" & Target.Row).Value = Time
End If
Me.Range("A" & Target.Row).Value = Date

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[J.W. Aldridge]

Thanx All.

Worked perfectly.

Apologize for any confusion, wanted date based on row change, time
based on different criteria, column change.

Thanx again!

 

[Bob Phillips]

Which worked, first or second in the post?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)