VBA to See Who Last Updated a Workbook

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:

ThisWorkbook.BuiltinDocumentProperties(“Last Author”)

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.

  1. Private Sub Workbook_Open()
  2.  
  3. ThisWorkbook.Windows(1).Caption = ThisWorkbook.Windows(1).Caption & _
  4. "   Last Updated By: " & _
  5. ThisWorkbook.BuiltinDocumentProperties("Last Author") & _
  6. " on " & _
  7. ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
  8.  
  9. End Sub

Morons beware! We have our eyes on you now.

8 thoughts on “VBA to See Who Last Updated a Workbook

  1. P.J.

    Love it! Nothing better than having evidence to strike down a moron that moron that pleads the 5th.

  2. Rich

    Another little fun trick to have in the toolbox. Is there any way of finding out the different available parameters for things like this? I assume that .BuiltInDocumentProperties has more scope than Last User and Last Save Time.

  3. Alan Sidman

    great idea Mike. I modified it to replace “ThisWorkbook” with “ActiveWorkbook” so that I could run it from my personal.xlsb file on any file with a sub routine. Thanks for the idea.

  4. Jim

    Rich, list of parameters:
    Title
    Subject
    Author
    Keywords
    Comments
    Template
    Last author
    Revision number
    Application name
    Last print date
    Creation date
    Last save time
    Total editing time
    Number of pages
    Number of words
    Number of characters
    Security
    Category
    Format
    Manager
    Company
    Number of bytes
    Number of lines
    Number of paragraphs
    Number of slides
    Number of notes
    Number of hidden Slides
    Number of multimedia clips
    Hyperlink base
    Number of characters (with spaces)
    Content type
    Content status
    Language
    Document version

  5. Jim

    Rich, list of parameters:
    Title
    Subject
    Author
    Keywords
    Comments
    Template
    Last author
    Revision number
    Application name
    Last print date
    Creation date
    Last save time
    Total editing time
    Number of pages
    Number of words
    Number of characters
    Security
    Category
    Format
    Manager
    Company
    Number of bytes
    Number of lines
    Number of paragraphs
    Number of slides
    Number of notes
    Number of hidden Slides
    Number of multimedia clips
    Hyperlink base
    Number of characters (with spaces)
    Content type
    Content status
    Language
    Document version

  6. Omar

    I am working on something that needs to know when the last save time was. The above property appears to include the last “autosaved” time. Here’s a bit of test code:

    Sub testsavetime()
    Debug.Print ThisWorkbook.FullName
    Debug.Print ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”)
    Debug.Print FileDateTime(ThisWorkbook.FullName)
    End Sub

    I get this result:
    \\Server\Data\Report.xlsb
    6/15/15 9:22:43 AM
    6/15/15 9:09:36 AM

    Am I understanding the difference in times correctly? The second (or earlier) time is the actual time I saved the file.

    I realize that they are identical at the point the file is opened, which would be the okay in the example above.

  7. Dave mac

    Hiya,

    I’ve got very basic excel knowledge but can this be modified so that the person who is currently updating a row automatically has their name date and time stamped in the last column.

    Then have the file lock the cell so that it doesn’t update when the next user opens the work book and inserts a new row or modifies a new blank row?

    Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *