| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time Stamping Cells | Excel Discussion (Misc queries) | |||
| Time Stamping A Cell | Excel Discussion (Misc queries) | |||
| automatic date | Excel Discussion (Misc queries) | |||
| Running averages and time stamping | Excel Worksheet Functions | |||
| Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
Linear Mode
