Automatic date stamping
 

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

Automatic date stamping
 

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

--
Gary''s Student - gsnu200752


"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

Automatic date stamping
 

=TODAY()
--
David Biddulph

"PK" wrote in message
...
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

Automatic date stamping
 

Hi Dave:

=TODAY() will change every time the worksheet is opened, whether updates
were made or not.
--
Gary''s Student - gsnu200752


"David Biddulph" wrote:

=TODAY()
--
David Biddulph

"PK" wrote in message
...
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

Automatic date stamping
 

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

Automatic date stamping
 

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

Automatic date stamping
 

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

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

Automatic date stamping
 

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

Automatic date stamping
 

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

Automatic date stamping
 

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
--
pk


"Gary''s Student" wrote:

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

Automatic date stamping
 

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

--
Gary''s Student - gsnu200752


"PK" wrote:

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
--
pk


"Gary''s Student" wrote:

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

Automatic date stamping
 

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

On Sat, 27 Oct 2007 08:51:01 -0700, PK wrote:

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

Automatic date stamping
 

<It may be a formatting issue

I still can't see how...

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Gary''s Student" wrote in message
...
| 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

Automatic date stamping
 

Perfect
Thank you
--
pk


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200752


"PK" wrote:

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
--
pk


"Gary''s Student" wrote:

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

Automatic date stamping
 

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200752


"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

Automatic date stamping
 

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




Jane N wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200752


"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

--

Dave Peterson

Automatic date stamping
 

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.

"Gary''s Student" wrote:

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