Automatic date stamping

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Hi Dave:

=TODAY() will change every time the worksheet is opened, whether updates
were made or not.
 
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
 
The date in the cell is 05 November 2101, the date on my computer is Saturday
October 27 2007
 
It appears that every time I carryout an edit it changes the date date to a
different one none of which have been correct
 
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
 
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
 
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
 
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
 
<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
 
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...
 
Can this be used for the same cell reference throughout a workbook without
adding the code to every tab?
Thanks.
 
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
 
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.
 
Back
Top