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

lovallee

Board Regular
Joined
Jul 8, 2002
Messages
216
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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
ABCD
1test10/22/200211:31
2
3
4
5
6
7
8test210/22/200211:31
9
10
11
12test310/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

Board Regular
Joined
Jul 8, 2002
Messages
216
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
 

Forum statistics

Threads
1,144,221
Messages
5,723,115
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top