Hi, I have a situation similar to D. West where I'd like for the current
time and date to be entered in col. A whenever a new row is populated with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"") like
Gary Brown suggested for D.West. That sort of worked, but I'd like for that
value to remain, not be subject to recalculation whenever something new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry

you might try to use Worksheet_Change event (go to sheet tab, right-
click on it, click Show code, instead of "(General)" select
"Worksheet" and then "Change" in the rightmost window)

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Selection.Row - 1, 1) = Time
End Sub

provided entering data moves you 1 row down
however this will cause time to be entered to cells in 1st column also
on editing any cell

HIH

Hi Jarek. I couldn't get it to work. I may have the Cell callout messed up.
Can you show me an example of what to put in for "Cells(Selection.Row - 1,
1) = Time". Thanks.
Larry

"Jarek Kujawa" wrote in message
...
you might try to use Worksheet_Change event (go to sheet tab, right-
click on it, click Show code, instead of "(General)" select
"Worksheet" and then "Change" in the rightmost window)

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Selection.Row - 1, 1) = Time
End Sub

provided entering data moves you 1 row down
however this will cause time to be entered to cells in 1st column also
on editing any cell

HIH

....Gord's solution is very good...

Private Sub WorkSheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Will add a timestamp to cell in column A when a change is made in
corresponding cell in row of column B

The timestamp does not update unless column A is edited.

This is sheet event code. Right-click on the sheet tab and "Viedw Code"

Copy/paste into that sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 15:47:59 -0400, "Larry"
wrote:

Hi, I have a situation similar to D. West where I'd like for the current
time and date to be entered in col. A whenever a new row is populated with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"") like
Gary Brown suggested for D.West. That sort of worked, but I'd like for that
value to remain, not be subject to recalculation whenever something new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry

Gord, thanks. Your suggestion seems to work great. Now, I don't really want
the date stamp in column A. How do I change the date stamp to, say, column
E?
Thanks.
Larry

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Private Sub WorkSheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Will add a timestamp to cell in column A when a change is made in
corresponding cell in row of column B

The timestamp does not update unless column A is edited.

This is sheet event code. Right-click on the sheet tab and "Viedw Code"

Copy/paste into that sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 15:47:59 -0400, "Larry"
wrote:

Hi, I have a situation similar to D. West where I'd like for the current
time and date to be entered in col. A whenever a new row is populated with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"")
like
Gary Brown suggested for D.West. That sort of worked, but I'd like for
that
value to remain, not be subject to recalculation whenever something new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry

Column E Offset from column B would be

..Offset(0, 3) instead of .Offset(0, -1)

Offset(Row, Column) is the syntax.


Gord

On Fri, 15 Aug 2008 18:28:45 -0400, "Larry"
wrote:

Gord, thanks. Your suggestion seems to work great. Now, I don't really want
the date stamp in column A. How do I change the date stamp to, say, column
E?
Thanks.
Larry

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Private Sub WorkSheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Will add a timestamp to cell in column A when a change is made in
corresponding cell in row of column B

The timestamp does not update unless column A is edited.

This is sheet event code. Right-click on the sheet tab and "Viedw Code"

Copy/paste into that sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 15:47:59 -0400, "Larry"
wrote:

Hi, I have a situation similar to D. West where I'd like for the current
time and date to be entered in col. A whenever a new row is populated with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"")
like
Gary Brown suggested for D.West. That sort of worked, but I'd like for
that
value to remain, not be subject to recalculation whenever something new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry

This worked!!! You guys are lifesavers! Thanks so much. I do have one other
question. Is there any way to change it to regular time instead of military
time?

"Gord Dibben" wrote:

Column E Offset from column B would be

..Offset(0, 3) instead of .Offset(0, -1)

Offset(Row, Column) is the syntax.


Gord

On Fri, 15 Aug 2008 18:28:45 -0400, "Larry"
wrote:

Gord, thanks. Your suggestion seems to work great. Now, I don't really want
the date stamp in column A. How do I change the date stamp to, say, column
E?
Thanks.
Larry

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Private Sub WorkSheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Will add a timestamp to cell in column A when a change is made in
corresponding cell in row of column B

The timestamp does not update unless column A is edited.

This is sheet event code. Right-click on the sheet tab and "Viedw Code"

Copy/paste into that sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 15:47:59 -0400, "Larry"
wrote:

Hi, I have a situation similar to D. West where I'd like for the current
time and date to be entered in col. A whenever a new row is populated with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"")
like
Gary Brown suggested for D.West. That sort of worked, but I'd like for
that
value to remain, not be subject to recalculation whenever something new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry

re-format yr cells
HIH

I am currently doing something similar, where data inputted in column A will
insert the date into column E.

This is to copied through the whole worksheet (well down to about 500ish
rows)

I have used the formula:

=IF(COUNTA(A102)<0,NOW(),"")

In column E and inserted the following in visual basics:

Private Sub WorkSheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value < "" Then
..Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True

End Sub

However, redaing the below, it is likely that the above is incorrect (offset
values error??)

Please could someone help and advise,

NB, this will be used on machines that use XP office, 2003 & 2007.

Thanks

Aaron


"D. West" wrote in message
...
This worked!!! You guys are lifesavers! Thanks so much. I do have one
other
question. Is there any way to change it to regular time instead of
military
time?

"Gord Dibben" wrote:

Column E Offset from column B would be

..Offset(0, 3) instead of .Offset(0, -1)

Offset(Row, Column) is the syntax.


Gord

On Fri, 15 Aug 2008 18:28:45 -0400, "Larry"
wrote:

Gord, thanks. Your suggestion seems to work great. Now, I don't really
want
the date stamp in column A. How do I change the date stamp to, say,
column
E?
Thanks.
Larry

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Private Sub WorkSheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value < "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Will add a timestamp to cell in column A when a change is made in
corresponding cell in row of column B

The timestamp does not update unless column A is edited.

This is sheet event code. Right-click on the sheet tab and "Viedw
Code"

Copy/paste into that sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 15:47:59 -0400, "Larry"
wrote:

Hi, I have a situation similar to D. West where I'd like for the
current
time and date to be entered in col. A whenever a new row is populated
with
values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<0,NOW(),"")
like
Gary Brown suggested for D.West. That sort of worked, but I'd like for
that
value to remain, not be subject to recalculation whenever something
new is
added to that or other rows. Any way to specify that value to remain
unchanged once it's calculated the first time? Thanks.
Larry