Have you always wanted to prove that your co-workers are morons intent on making you look bad? Well today, I’ll share a trick that will make all you paranoid schizophrenics very happy.
A friend of mine asked if there is a way to easily see who last updated a particular workbook.
Apparently, he works in an environment where multiple people are editing workbooks on a share drive.
Of course, he knows that the workbook properties will show who last saved changes, but he wants to see that info as soon has he opens the file.
My answer to him was to hijack the Excel Title Bar. The Title Bar usually only shows the name of the workbook. But with a bit of simple VBA,
you can have it also show the last person who updated the file and the date of the last save.
The basis for this code is relatively straight forward.
We can use the document properties (last author and last save time) to augment the Title Bar with more useful information.
There is a workbook property called BuiltinDocumentProperties which lets you get to the document properties for your workbook.
To get the name of the username of the last person who updated and saved the file, you can use:
To get the date of the last save, you can use:
ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”)
As you can see in the code below, we are using these two statements to add these properties to the window caption of “ThisWorkbooks”.
You can either paste this code in a standard module that you can run on demand, or you can get fancy and place this code in the workbook’s OPEN event so that it automatically runs on open.
- Private Sub Workbook_Open()
- ThisWorkbook.Windows(1).Caption = ThisWorkbook.Windows(1).Caption & _
- " Last Updated By: " & _
- ThisWorkbook.BuiltinDocumentProperties("Last Author") & _
- " on " & _
- ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
- End Sub
Morons beware! We have our eyes on you now.