how date stamp only when changes occur?
how date stamp only when changes occur?
(OP)
Hi folks
I open a spreadsheet, leave it open for a while and later want to close it. Sometimes I make changes to it and sometimes I don't. I've got the NOW() command in a cell, so every time I go to close the sheet I'm promped if I want to save the changes. The NOW() command that is forcing the prompt to occur. The problem is that I often don't remember if I made changes to the spreadsheet, so don't always know how to answer the prompt. Is there another easier/foolproof way to ensure that I keep the date of the lastest revision in the spreadsheet ?
I open a spreadsheet, leave it open for a while and later want to close it. Sometimes I make changes to it and sometimes I don't. I've got the NOW() command in a cell, so every time I go to close the sheet I'm promped if I want to save the changes. The NOW() command that is forcing the prompt to occur. The problem is that I often don't remember if I made changes to the spreadsheet, so don't always know how to answer the prompt. Is there another easier/foolproof way to ensure that I keep the date of the lastest revision in the spreadsheet ?





RE: how date stamp only when changes occur?
TTFN
RE: how date stamp only when changes occur?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Range("a1").Value = Now()
End Sub
Any time you make a change, latest date and time will be recorded in cell A1.
RE: how date stamp only when changes occur?
Many thanks. Unfortunately I don't how to activate visual basic, or just how to use the code you did up for me.
RE: how date stamp only when changes occur?
Click on Tools - Macros - Visual Basic Editor
Should bring up VBasic
Click on View - Project Explorer
Select VBAProject with your workbook name, say book1.xls :
+VBAProject(Book1)
Double click on ThisWorkbook
On right hand pane type in code as above
Click on File - Close and Return
Save file
Should work OK. Better save back up copy first though!!
RE: how date stamp only when changes occur?
RE: how date stamp only when changes occur?
If you made changes they are saved. If no changes then the information on disk is overwritten with the same information from memory.
Rick Kitson MBA P.Eng
Construction Project Management
From conception to completion
www.kitsonengineering.com
RE: how date stamp only when changes occur?
RE: how date stamp only when changes occur?
BUT, it looks like the macro removes ability to undo an operation. Can this be brought back Mutt?
RE: how date stamp only when changes occur?
RE: how date stamp only when changes occur?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("a1").Value = Now()
End Sub
This will only update the value in A1 to the current date/time when you save the workbook. If you didn't change anything, you are not prompted to save the workbook, and the event/update doesn't occur.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: how date stamp only when changes occur?
A more fool proof method (but I haven't tested it), is to put the NOW() function in another sheet, and link the sheet. When you open the spreadsheet, Excel will ask if you want to update the external links. If you answer "no", then the sheet has not changed, and will not prompt you to resave unless you make changes. Of course, all cells will display the values that they displayed the last time you saved, even if those values depend on an old NOW() value.
-JimB