Code to automaticly enter the date and time of a new entry i

[lovallee]

Hello!

Database often provide an automatic function that enter the "last modified date" for a specific entry.

I am building a simple (one-table, 12 fields) database in Excel.

For each addition/modification to a specific entry (line), I would like the actual date and time of the addition/modification to be automaticly entered in the cell corresponding to the first field of the new/modified entry (line).

I guest I won't have to the choice to use VBA...but I have no idea how to do that.

Anyone can help?

Thank you

Louis

 

[NateO]

Hello Louis,

You'll want a worksheet_change event. Not sure about your layout, but I cooked up the following procedure to slam an updated date & time in column B if an entry is made in cells a1:a12.

Right click on your worksheet, click view code, and insert the following procedure in your now opened module:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [a1:a12]) Is Nothing Then _
Target(, 2) = Now 'With Index, 2 is B, 3 is C, etc...
End Sub</pre>


My spreadsheet looked like:

Book3
	A	B	C	D
1	test	10/22/200211:31
2
3
4
5
6
7
8	test2	10/22/200211:31
9
10
11
12	test3	10/22/200211:26
Sheet2

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-22 12:32

 

[lovallee]

Thank you very much Nate!

Based on your post, I ended up with the following code, which enter the date in a specified column whichever field is modified.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [a1:n65000]) Is Nothing Then _
Target(, 5 - Target.Column) = Now
End Sub


Louis