Automatic date stamping

[Guest]

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

 

[Guest]

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

 

[David Biddulph]

=TODAY()

 

[Guest]

Hi Dave:

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[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

 

[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

 

[Guest]

Perfect
Thank you
--
pk

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

 

[Jane N]

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

 

[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

 

[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.