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 changed. (Unsaved changes, of course, are not really a lasting change at all.) The following macro will return the date that a workbook was saved and store that date in cell A1:

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

To use the macro, just replace the D:\MyFolder\MyFile.xls 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. Choose Share Workbook from the Tools menu. Excel displays the Share Workbook dialog box.
2. Make sure the Editing tab is displayed. (Click here to see a related figure.)
3. Select the Allow Changes check box.
4. Display the Advanced tab. (Click here to see a related figure.)
5. Make sure the Keep Change History radio button is selected.
6. Using the other controls in the dialog box, select the tracking options you want used with the workbook.
7. Click on OK.
8. Choose Track Changes from the Tools menu, then choose Highlight Changes from the submenu. Excel displays the Highlight Changes dialog box. (Click here to see a related figure.)
9. Make sure the List Changes on a New Sheet check box is selected.
10. 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.