Automatic date stamping

P

PK

I Woukd like to a date stamp to to entered in to a cell within aspreadsheet
and refreshed each time the spreadsheet is updated.

Is this possible
 
G

Gary''s Student

Let's use cell A1 to record the date a change is made. Enter the following
worksheet event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Application.EnableEvents = True
End Sub

You can replace A1 with any cell you like.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
G

Gary''s Student

Hi Dave:

=TODAY() will change every time the worksheet is opened, whether updates
were made or not.
 
P

PK

Thank you for that however I find that the date being entered is incorrect
and does not match the date on my computer.

Thank you again
 
G

Gary''s Student

What is the date that you see in the cell and what is the date on your
computer??
 
P

PK

The date in the cell is 05 November 2101, the date on my computer is Saturday
October 27 2007
 
P

PK

It appears that every time I carryout an edit it changes the date date to a
different one none of which have been correct
 
G

Gary''s Student

It may be a formatting issue.

1. Remove the previous macro
2. Instert this one in its place:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Range("A1").NumberFormat = "dd mmmm yyyy"
Application.EnableEvents = True
End Sub
 
P

PK

Thats great, exactly what I was after.If I want to put a time stamp on it
also do I just enter time after the word "date".

Thanks again for your help
 
G

Gary''s Student

This version includes both date & time:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Now
Range("A1").NumberFormat = "dd mmmm yyyy HH:MM:SS"
Application.EnableEvents = True
End Sub
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Format(Now, "dd mmmm yyyy h:mm:ss")
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
N

Niek Otten

<It may be a formatting issue>

I still can't see how...

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| It may be a formatting issue.
|
| 1. Remove the previous macro
| 2. Instert this one in its place:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| Application.EnableEvents = False
| Range("A1").Value = Date
| Range("A1").NumberFormat = "dd mmmm yyyy"
| Application.EnableEvents = True
| End Sub
|
| --
| Gary''s Student - gsnu200752
|
|
| "PK" wrote:
|
| > It appears that every time I carryout an edit it changes the date date to a
| > different one none of which have been correct
| > --
| > pk
| >
| >
| > "Gary''s Student" wrote:
| >
| > > What is the date that you see in the cell and what is the date on your
| > > computer??
| > > --
| > > Gary''s Student - gsnu200752
| > >
| > >
| > > "PK" wrote:
| > >
| > > > Thank you for that however I find that the date being entered is incorrect
| > > > and does not match the date on my computer.
| > > >
| > > > Thank you again
| > > > --
| > > > pk
| > > >
| > > >
| > > > "PK" wrote:
| > > >
| > > > > I Woukd like to a date stamp to to entered in to a cell within aspreadsheet
| > > > > and refreshed each time the spreadsheet is updated.
| > > > >
| > > > > Is this possible
| > > > > --
| > > > > pk
 
P

PK

Perfect
Thank you
--
pk


Gary''s Student said:
This version includes both date & time:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Now
Range("A1").NumberFormat = "dd mmmm yyyy HH:MM:SS"
Application.EnableEvents = True
End Sub
Click to expand...
 
J

Jane N

Can this be used for the same cell reference throughout a workbook without
adding the code to every tab?
Thanks.
 
D

Dave Peterson

You can remove any of the worksheet_change event code you added to any of the
sheets and use a workbook event instead.

This goes behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Sh.Range("a1").Value = Date
Application.EnableEvents = True
End Sub
 
M

mjg916

This is what I was looking for. How do you set it for the whole column?
Tried A1-A100 and didn't work. Sorry, I'm a coding newb.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to automate update on weekly reports spreadsheet 0
if/then insert date but don't want automatic updates 7
Enable Iterative Calculations 0
Date stamp user 7
Auto Stamp Date 13
Date Stamp 7
Date Stamp in Footer 1
Date Stamp by User and cell value 8

Top