Date Last Saved/Modified

[animas]

I need a function to show date last saved or modified in a cell which will get updated each time workbook is saved/closed.

How can I do this?

 

[jbeaucaire]

You can add this capability to your workbook, it's not a built in function. Here's the UDF code for the new function:

Function LastSaved() As Date
    Application.Volatile
    LastSaved = ThisWorkbook.BuiltinDocumentProperties(12)
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
=======

In a cell, enter this formula:

=LASTSAVED()

The cell will not return a value properly until the sheet has been saved. Format the cell to display the date/time to your liking.

 

[animas]

Thanks.

I put the formula in each sheet. But when I update a different sheet all worksheets cells with that formula is updated. It seems the function is updated at wrokbook level.
How can I keep updated values worksheetwise?

 

[jbeaucaire]

This is completely different from your original question. If you're asking for a way to put a time stamp on each sheet individually any time there are any changes anywhere on each sheet, then that would require a worksheet_change macro.

For each sheet where you want this to occur:

1) Right-click the sheet tab
2) Select View Code from the context menu popup
3) Paste this code into the sheet module that appears:

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
    
    Range("A1") = Now()

Application.EnableEvents = True
End Sub

4) Edit the highlighted code to the cell of your choosing
5) Close the editor
6) Format the cell to display the date/time stamp to your liking
7) Save your sheet
8) Repeat with other sheets.

 

[animas]

Perfect. I will put both methods in use.

 

[tcrank472]

You can add this capability to your workbook, it's not a built in function. Here's the UDF code for the new function:

Function LastSaved() As Date
    Application.Volatile
    LastSaved = ThisWorkbook.BuiltinDocumentProperties(12)
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
=======

In a cell, enter this formula:

=LASTSAVED()

The cell will not return a value properly until the sheet has been saved. Format the cell to display the date/time to your liking.

I've used your LastSaved() function with good success and wondered if you have a similar function to identify and record on the sheet the user name of the person performing the last save? Thanks!
Tom

 

[jbeaucaire]

This will give you the last author:

Function LastAuthor() As String
    Application.Volatile
    LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

=LastAuthor()

 

[Jeff-Q]

I knew there had to be an easy way to do this. I just put this simple function code to use. I can always rely on the gurus at MrExcel. Thanks!

 

[How_Do_I]

An interesting topic... Is there a way to record the last 3 saves of the workbook lets say in A1:A3 of Sheet1 please?

 

[Maheshp]

you can use Track changes option from Review Tab (Excel 2007) as well