×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

how date stamp only when changes occur?
4

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 ?

RE: how date stamp only when changes occur?

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

TTFN

RE: how date stamp only when changes occur?

4
Could do this in VBasic under thisworkbook object :

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?

(OP)
Mutt

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?

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

RE: how date stamp only when changes occur?

(OP)
Excellent Mutt. Works like a charm. Many thanks.

RE: how date stamp only when changes occur?

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
www.kitsonengineering.com

RE: how date stamp only when changes occur?

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.

RE: how date stamp only when changes occur?

(OP)
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?

RE: how date stamp only when changes occur?

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

RE: how date stamp only when changes occur?

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.

RE: how date stamp only when changes occur?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources