Noting When a Workbook was Changed

In an environment where multiple people work on the same workbook, you may want a way to keep track of when people last changed a workbook. There are a couple of ways you can approach this task. One is to simply figure out when a workbook was last saved. This approach works well if you assume that any changes to the workbook are always saved. (Unsaved changes, of course, are not really a lasting change at all.) The following macro returns the date that a workbook was saved and stores that date in cell A1:

Sub DateLastModified()
    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile("D:\MyFolder\MyFile.xlsx")
    Cells(1, 1) = f.DateLastModified
End Sub

To use the macro, just replace the D:\MyFolder\MyFile.xlsx file specification with whatever is appropriate for you.

If you want a history sheet of who did what with your workbook, then a different approach is necessary. Perhaps the best solution is to try Excel's sharing feature, which can be configured to keep a history log for a workbook. Follow these steps:

1. Display the Review tab of the ribbon.
2. In the Changes group click the Share Workbook tool. Excel displays the Share Workbook dialog box.
3. Make sure the Editing tab is displayed. (See Figure 1.)



Figure 1. The Editing tab of the Share Workbook dialog box.

4. Select the Allow Changes check box.
5. Display the Advanced tab. (See Figure 2.)



Figure 2. The Advanced tab of the Share Workbook dialog box.

6. Make sure the Keep Change History radio button is selected.
7. Using the other controls in the dialog box, select the tracking options you want used with the workbook.
8. Click on OK.
9. In the Changes group click the Track Changes tool, then choose Highlight Changes from the submenu. Excel displays the Highlight Changes dialog box.
10. Make sure the List Changes on a New Sheet check box is selected.
11. Click OK.

As changes are made to the workbook, Excel tracks those changes (along with who made them) and puts them in a separate worksheet so you can review them later.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7924) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Noting When a Workbook was Changed.