Software

Ask something
Discussion Locked

Last Date Updated Macro

0 helpful
5 replies

by ljackson.desoto

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I've got a user who needs to know when the last date of a file was updated (i.e. data changed). I know that properties gives acessed and modified info, but it the file is saved and no changes made the properties will show that file being modified even though the actual data hasn't changed. Any ideas out there?

5 replies

by jboardman

Use the properties on the individual fields instead. It add a bit of a load since it will update the record every time a field is changed, but it won't update the date field if nothing is changed.

by charles byrne

Hello,

Open the File you want to monitor Goto the VB editor ALT+F11. In the Project Explorer click on "ThisWorkbook" and insert the follwing code.

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

Dim strMsg
strMsg = "Last Changed : " & Format(Now, "mm/dd/yyyy hh:mm am/pm")
strMsg = strMsg & ", Sheet: " & Sh.Name
ThisWorkbook.Names.Add "LastChanged", strMsg

End Sub

In XL Version 5 they used to let you write to the document properties, but it appears to be read only now. That's why I chose to use a workbook name. You could hide the name so that users couldn't modify it. I hope this gets you in the right direction.

Charles

This discussion is closed to new replies.