Automatically update a cell in Excel 3

[MarcLodge]

Hi All,
I'm sure this is an easy one, but my Excel knowledge is letting me down at the moment..... I have a spreadsheet that contains rows of data. The last column I want to be a date time column that is automatically updated as each row is added. I've managed to get it to do this by use of the
=IF(A7=""," ",NOW())
but this doesn't save the time and updates everytime I look at the sheet.

Thanks in anticipation

Marc

 

[xlbo]

Have a look at this thread - should give you an idea:
thread707-575511

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[MarcLodge]

Thanks for that Geoff, but it's not quite what I need. I have 3 worksheets in the book, and each one contains a table of data. As each new row is added, I want the last column to reflect the data/time that new row was inserted. The thread mentioned adding a macro for each sheet, but I guess I want an on event macro at cell level. Is this possible?

Marc

 

[xlbo]

That's pretty much what that thread was about - check out the "Sheet Change" event - should do what you need

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Zathras]

Geoff is correct. The only way to do what you want is with VBA. Here is one way to accomplish what you want:

In each worksheet:
[blue]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  EnsureTimeStamp (Target.Row)
End Sub

[/color]

Then in a separate code module:
[blue]

Option Explicit
Const COL_TIMESTAMP = 7

[green]

'Column "G"

[/color]

Const FIRST_DATA_ROW = 3

Sub EnsureTimeStamp(ARow As Long)
  If ARow >= FIRST_DATA_ROW Then
    If Cells(ARow, COL_TIMESTAMP).Text = "" Then
      Cells(ARow, COL_TIMESTAMP) = Now
    End If
  End If
End Sub

[/color]
Adjust the constants for your specific situation.
If you have some rows below your data area (totals, etc.) all you have to do is put something in the timestamp column to inhibit the writing of a timestamp there. (Just a single space is sufficient.)

 

[MarcLodge]

Zathras, Geoff,
Thanks for your help and I think I'm almost there. I've copied the example Zatras gave, and added a little:

Option Explicit
Const COL_TIMESTAMP = 7 'Column "G"
Const COL_PLAYER = 1 'Column "A"
Const FIRST_DATA_ROW = 2

Sub EnsureTimeStamp(ARow As Long)
If ARow >= FIRST_DATA_ROW Then
If Cells(ARow, COL_PLAYER).Text <> &quot;&quot; Then
Cells(ARow, COL_TIMESTAMP) = Now
End If
End If
End Sub

What I would like to happen is that if the the value in columnn 1 changes, and is not a space, then column 7 to be updated. At the moment this takes no notice of whether column 1 has changed or not, but just updates as I move around the sheet.

Any ideas

Marc

 

[xlbo]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
EnsureTimeStamp (Target.Row, Target.Column)
End Sub


Option Explicit
Const COL_TIMESTAMP = 7 'Column &quot;G&quot;
Const COL_PLAYER = 1 'Column &quot;A&quot;
Const FIRST_DATA_ROW = 2

Sub EnsureTimeStamp(ARow As Long, ACol as integer)
If ARow >= FIRST_DATA_ROW Then
If ACol = 1 then
If Cells(ARow, COL_PLAYER).Text <> &quot; &quot; Then
Cells(ARow, COL_TIMESTAMP) = Now
End If
End If
End If
End Sub


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[MarcLodge]

Geoff,
Thanks, and I'll get back to you when I've had a little play with it, as it doesn't work at the moment. I get an error on the private Sub when it tries to pass 2 variables to the Ensure... Sub
Marc

 

[MarcLodge]

Geoff, Zathras,
Try as I might I can't get it to work. I get an error on the Private Sub as it does not like passing two variables to the Ensure.
Any ideas

Marc

 

[GlennUK]

I couldn't either, so I put a &quot;Call&quot; in front of the Ensure, so now it reads ....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call EnsureTimeStamp(Target.Row, Target.Column)
End Sub


Hey presto, it works fine.

Cheers, Glenn.

 

[xlbo]

Double checked - needs the Call - I think because you are trying to run a public sub from a private sub

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[MarcLodge]

Thanks everybody, that works just perfect now.

Marc

 

[Zathras]

Geoff, what version of Excel are you running? I just pasted the code from my post into a new worksheet and it still works fine for me as posted (Excel 97 SR-1)

 

[xlbo]

Zathras - Just been XP'd (as of about 2 weeks ago)
Think it's probably that 'cos I've never had to use the Call operator before (when using 97)

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes