Auto Update Time Stamp?

[Leviathan]

Hello,

I am trying to have a cell (or cells) that log when the last change was made to a shared workbook.

I have tried a few options through VBA that I found through searching:

Public Function ModDate()​

ModDate = Format(FileDateTime(ActiveWorkbook.FullName), "m/d/yy hh:nn ampm")​

End Function​

or

Sub Last_Save()​

Range("E2").Value = Format(ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), "m/d/yy hh:nn ampm")​

End Sub​

or

Function LastModified() As Date​

LastModified = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")​

End Function​

Each work, but only each time I run the function. I want this to run automagically whenever there is a change (so that my boss can see how current the workbook data are).

I also tried to force the function to update:

Sub UpdateCell(D2)​

ActiveWorkbook.RefreshAll​

Application.OnTime Now + TimeValue("00:00:5"), "List1.UpdateCell"​

End Sub​

This didn't work at all.

Please help.

 

[Joe4]

Welcome to the Board!

You probably want to put your code in a Worksheet_Change event procedure, which is code that runs automatically when something is manually updated on the sheet.
Something like this: https://www.mrexcel.com/forum/excel-questions/706972-vba-code-autopost-date-time-stamp.html

For more on event procedures, read this: http://www.cpearson.com/excel/events.aspx

 

[Leviathan]

I appreciate the quick reply. I should have noted that I am a complete noob in VBA, so the more elementary the explanation the better.

That said, I'm not opposed to (attempt) learning. In a quick read of the second link, it notes that event procedures don't update on formula calculation. I am only really interested monitoring changes on the summary worksheet that calculates from the other sheets.

So, can I make an event procedure that works on formula calculations somehow? Or can I have it read from the entire workbook?

The formulas I posted above seemed to pull from the workbook's info/properties. I was hoping that I could continue in that direction (tracking last save is accurate enough). I thought/hoped that there was an easy way to make one of the above codes refresh on save.

Does that make sense?

 

[Joe4]

There is a Worksheet_Calculate event, if you want to search/read-up on that.

I am only really interested monitoring changes on the summary worksheet that calculates from the other sheets.

So, that implies that there are changes happening to these other sheets. How exactly are these changes happening?

 

[Leviathan]

This is a team project summary workbook. Each team member has a sheet. Each sheet is updated as projects progress. There is a summary page that calculates based on the combined team project progress. My boss wants to look at the summary page and be able to easily tell the last time anyone updated anything. It doesn't need to be member/sheet/project specific, she just wants to know if it has been lying fallow for two weeks.

I thought that would be an easy thing to put in... but nothing is ever easy.

 

[Joe4]

Looks like there is some good information here that might be helpful: http://www.ozgrid.com/VBA/track-changes.htm

Maybe this too: https://support.office.com/en-us/ar...workbook-22aea671-cac7-4fa3-845d-eeb23725bd15

 

[Leviathan]

I think I found what I needed.

Adding "Application.Volatile" to the VBA code seems to do the trick.

as in:

[FONT=&quot]Public Function ModDate()[/FONT]
[FONT=&quot] Application.Volatile[/FONT]
[FONT=&quot] ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")[/FONT]
[FONT=&quot]End Function

[/FONT]