how date stamp only when changes occur? 4

[drain]

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 ?

 

[IRstuff]

You can look at the Undo list to remind you of what you did.

TTFN

 

[Mutt]

Could do this in VBasic under thisworkbook object :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Range("a1&quot.Value = Now()

End Sub

Any time you make a change, latest date and time will be recorded in cell A1.

 

[drain]

Mutt

Many thanks. Unfortunately I don't how to activate visual basic, or just how to use the code you did up for me.

 

[Mutt]

Easy intro :
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!!

 

[drain]

Excellent Mutt. Works like a charm. Many thanks.

 

[RDK]

Why not just save it everytime?

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

http://www.kitsonengineering.com

 

[melone]

Won't this give the user a visual representation of when the last update was performed? This is useful in revision control especially if the spreadsheet is accessed by multiple users.

 

[drain]

The main reason I wanted it is because I often start a spreadsheet, get interrupted and when it comes time to close Excel for the day, I'm asked if I want to save my changes. If I know the last revision time it sometimes makes it easier to decide whether or not to save the changes.

BUT, it looks like the macro removes ability to undo an operation. Can this be brought back Mutt?

 

[Mutt]

Not that I'm aware of - when macro runs, undos are lost - let me think on it though - maybe a way around it.

 

[joerd]

You could put the revision date in the Workbook_BeforeSave procedure, just like Mutt's post, only use the following instead:

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.

 

[jabaldauf]

Excel only updates the spreadsheet when cells on the sheet are changed. The NOW() cell will update when the sheet is first opened, and again anytime any cell is changed. Check the value of this cell as a clue to whether you changed the sheet or not.

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