time stamp based on action in column.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 10 Jan 2007 15:41 GMT

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

Reply to this Message

Bob Phillips - 10 Jan 2007 15:59 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi.
> I need to place a date stamp on the row in column A, that is triggered
[quoted text clipped - 12 lines]
>
> Thanx

Reply to this Message

J.W. Aldridge - 10 Jan 2007 16:19 GMT

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.

Reply to this Message

Don Guillett - 10 Jan 2007 17:06 GMT

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?

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

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

Reply to this Message

Bob Phillips - 10 Jan 2007 20:18 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

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

Reply to this Message

J.W. Aldridge - 10 Jan 2007 20:33 GMT

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.

Reply to this Message

Bob Phillips - 10 Jan 2007 23:00 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Bob, I tried that one, and the date stamp works fine but, the time
> stamp in column B still does not trigger.
[quoted text clipped - 16 lines]
>
> Thanx.

Reply to this Message

J.W. Aldridge - 11 Jan 2007 00:38 GMT

Thanx All.

Worked perfectly.

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

Thanx again!

Reply to this Message

Bob Phillips - 11 Jan 2007 00:53 GMT

Which worked, first or second in the post?

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Thanx All.
>
[quoted text clipped - 4 lines]
>
> Thanx again!

Reply to this Message

Don Guillett - 10 Jan 2007 16:18 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi.
> I need to place a date stamp on the row in column A, that is triggered
[quoted text clipped - 12 lines]
>
> Thanx

Reply to this Message