Auto Date 4 changing fields in a line

[Guest]

Hi, Is there a way to update a field / column with the date I change a record
in that row? I wish to have only specific cells linked to that, to include
range of cells. Also prefer data type date for easy
view/understanding/sorting: i.e. yymmdd.
Thanks in advance.

- Subsequently, anyone interested in Investment Spreadsheets / math please
feel free to email me: nasgentech at yahoo; have / looking for math
equations, resources/solutions.

-Nastech

 

[Dave Peterson]

J.E. McGimpsey shows how at:
http://www.mcgimpsey.com/excel/timestamp.html

And with a couple of minor modifications to check multiple columns and update a
single column, you could have something like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Me.Range("A:A,C:C,F:H"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "Z")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You can change this line to look just in the columns you want:
If Not Intersect(Me.Range("A:A,C:C,F:H"), .Cells) Is Nothing Then

You can change this line to update a different column (I used Z).
With Me.Cells(.Row, "Z")

And you can format the date/time statement anyway you like:
..NumberFormat = "dd mmm yyyy hh:mm:ss"

 

[Guest]

That is Great, thankyou !!! I have been documenting some of the neat stuff
here so I can do more. So I can do more on my own / don't know how to
initiate this; Does this fall under the heading of macro / where do I click
/ insert this??
Thanks

 

[Dave Peterson]

This does fall under the giant category of Macro.

But it also falls under the category of worksheet events. You don't need to
click this macro to run it. The code is placed under the worksheet that should
have this behavior.

Rightclick on the worksheet tab, select view code and paste this into the code
window that you see.

There are lots of these worksheet/workbook events that you can tie into. Here's
a couple of links that you may want to bookmark:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm