Update date in a cell when any changes are made and saved on a worksheet

[loumarday]

Hi there,
I'd like to have a date displayed on my worksheet that updates ONLY when the worksheet is saved with changes. If I just open it, look at it and close it without saving I want the date to remain unchanged from the last time the worksheet was modified. Can this be done?

1. I've tried some VBA but it gives me the current date/time stamp each time I open the workbook i.e. each time the macro runs.
For example, I used this code I found on another forum but it doesn't give me what I want.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A2") = Date
End Sub

2. Plus, if I want to the date to be displayed in cell A2 - what function would I need to put in there?

Thanks!

[JBeaucaire]

Remove that macro and replace it with this:

Please Login or Register  to view this content.

[loumarday]

Thanks, I'll try this code but what goes into cell A2?

[JBeaucaire]

It looks like that code would put the current date. You could change Date to Now and it would put the entire date/time stamp into that cell, then you could format that cell to display any way you wanted.

[loumarday]

Unfortunately it's not working for me. Every time I open the file it updates the date to "now". What I'm looking for is if I open the file on 6/7/2012 but the last time it was saved was on 6/1/2012 I want 6/1/2012 to be displayed. Thanks anyway.

[JBeaucaire]

The code I gave you does not change the date to the current date unless you make a physical change to something in the workbook. Perhaps you have other code that does? Do you have a Workbook_Open macro in there somewhere?

[loumarday]

Hmm, I am missing something then. If you would be kind enough to take a look at my workbook, I would be eternally grateful. I'm missing the connection of how to get it to show up in the cell where I want it (AK1). Thank you!!

test.xls

[JBeaucaire]

The macro I gave you is to put a timestamp on that sheet if a change is made anywhere on any sheet in the workbook. Workbook level events do not go into Sheet modules, they go into the ThisWorkbook module. Take it out of the sheet module and put it in the ThisWorkbook module.

If you want a version of that macro that only works on the 1. People Sheet (meaning you can edit the other sheets without causing a timestamp update), you can put this into that sheet module:

Please Login or Register  to view this content.

[loumarday]

I'm sorry to be so thick about this but I used the new code you posted for just changes on the People Sheet tab but no date shows up in cell AK1 when I make and save changes. Do I need to enter anything into cell AK1 to make that code display the date? Thank you again for your help.

[JBeaucaire]

The new code does go in the sheet module. The original code does not.

[loumarday]

I'm sorry, we're clearly not understanding each other. I've put the code in the sheet module but still no date appears when I make and save changes. Could you just fix it in the sheet I attached above and reattach it.

[JBeaucaire]

No, you post your edited sheet and let me see what you've done.

[loumarday]

All I've done is paste your code in...I don't know what to do from there.

[JBeaucaire]

Your workbook worked for me immediately.

Close your Excel completely, reopen and try again. I think you just got stuck with macros disabled temporarily.

[loumarday]

Yep, that was it. Thanks again for your help.

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.