Setting time of last update in a cell
 

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

Maybe set the date when the user saves the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
With ThisWorkbook
With Worksheets("Sheet1")
.Range("A1").Value = "Last Saved By " _
& Environ("UserName") & " " & Now
End With
End With
End Sub

Or automatically save the workbook when user hits Close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
With Worksheets("Sheet1")
.Range("A1").Value = "Last Saved By " _
& Environ("UserName") & " " & Now
End With
.Save
End With
End Sub

Whichever of these you choose would be entered into the Thisworkbook module.

Right-click on the Excel logo left of "File" on menu bar. and "View Code"

Paste into that module.


Gord Dibben MS Excel MVP

On Mon, 15 Jan 2007 12:44:01 -0800, R Mallory
wrote:

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

A1 will only change if the worksheet is updated.


If you change a cell's value via editting, then A1 should update.
--
Gary's Student
gsnu200701


"R Mallory" wrote:

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

To Gord and Gary's student.

Thank you both. The first solution is closer to what I want as if I open
the file and save it with no changes, there would be no need to know of the
new date/time stamp. I don't know what is different today (maybe that my
machine was re-booted), but it works exactly like gary's student describes.
If I change a cell, the timestamp updates. Even if I wait for a while to
save it, the cell contains the time of the last update (not time file was
saved) and that is just hunky dory for me. Thank you again, the both of you
for being willing to help out a novice.

"Gary''s Student" wrote:

A1 will only change if the worksheet is updated.


If you change a cell's value via editting, then A1 should update.
--
Gary's Student
gsnu200701


"R Mallory" wrote:

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

I just noticed something strange. With the code in the spreadsheet, I don't
have the ability to undo entries. Is that a known side-effect?

"R Mallory" wrote:

To Gord and Gary's student.

Thank you both. The first solution is closer to what I want as if I open
the file and save it with no changes, there would be no need to know of the
new date/time stamp. I don't know what is different today (maybe that my
machine was re-booted), but it works exactly like gary's student describes.
If I change a cell, the timestamp updates. Even if I wait for a while to
save it, the cell contains the time of the last update (not time file was
saved) and that is just hunky dory for me. Thank you again, the both of you
for being willing to help out a novice.

"Gary''s Student" wrote:

A1 will only change if the worksheet is updated.


If you change a cell's value via editting, then A1 should update.
--
Gary's Student
gsnu200701


"R Mallory" wrote:

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.

Setting time of last update in a cell
 

Yes

The undo stack is deleted when code runs.

The worksheet_change event code will clear the undo stack every time you make a
change on the worksheet which triggers the code.


Gord

On Tue, 16 Jan 2007 13:26:03 -0800, R Mallory
wrote:

I just noticed something strange. With the code in the spreadsheet, I don't
have the ability to undo entries. Is that a known side-effect?

"R Mallory" wrote:

To Gord and Gary's student.

Thank you both. The first solution is closer to what I want as if I open
the file and save it with no changes, there would be no need to know of the
new date/time stamp. I don't know what is different today (maybe that my
machine was re-booted), but it works exactly like gary's student describes.
If I change a cell, the timestamp updates. Even if I wait for a while to
save it, the cell contains the time of the last update (not time file was
saved) and that is just hunky dory for me. Thank you again, the both of you
for being willing to help out a novice.

"Gary''s Student" wrote:

A1 will only change if the worksheet is updated.


If you change a cell's value via editting, then A1 should update.
--
Gary's Student
gsnu200701


"R Mallory" wrote:

Thank you so much for your help. I pasted the code, then used file return to
Excel to get out. I saved the file, but I don't see any value in A1. I've
tried formatting A1 to text and date, but there is nothing. Did I miss
something? Funny thing, my wife's family nickname is Garry.

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub

Whenever the sheet is changed the date/time is placed in cell A1

To insert the code:
1. Right-click the tabname at the bottom
2. Select View Code (near the bottom)
3. paste the stuff in
--
Gary's Student
gsnu200701


"R Mallory" wrote:

I would like to display the last time a spreadsheet was updated as it is
updated by more than one person. It would be great if I did not have to
count on each person putting the time of their update in a cell. The Now
function won't work because that changes when you open the file. I am using
Excel 2003.

Thanks for any help.