Automatically show last update day/time and possible user

[DPChristman]

I am looking for a way (probably a macro), to automatically save the date/time in a cell every time a file is updated
I have a large spreadsheet, that is frequently updated by multiple users.

I am hoping to have a cell show the date, time, and possibly the username of the last person who saved the file.

Thoughts?

 

[MrTeeny]

Sticking the data in a cell is relatively simple just use soething like

    With CreateObject("WScript.Network")
        Range("A1").Value = .UserName & " " & Time() & " " & Date


    End With

Then take a look at the Worksheet_Change or Workbook_BeforeClose routines to decide when you want that data to be entered on your sheet, I've always found Workbook_BeforeClose to be a bit flaky but probably just not using it correctly as it's not something I need.

 

[iliace]

For example, if you put this code in ThisWorkbook module of your file, every time the file is saved A1 is updated with date/time and A2 is updated with the current user name:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Sheet1.Range("A1").Value = Now
  Sheet1.Range("A2").Value = Application.UserName
End Sub

This approach will not work with a shared workbook, because the cells will not automatically be refreshed if another user saves. But if this is an exclusive-mode file that each user opens one at a time, then you could do something like the above.