Creating A Usage Log

Category: General VBA | [Item URL]

This tip describes how to create a usage log that keeps track who opens a workbook, and at what time.

If a workbook's ThisWorkbook code module contains a procedure named Workbook_Open, that procedure is executed whenever the workbook is opened. Add the following procedure to the ThisWorkbook code module.

Private Sub Workbook_Open()
    Open ThisWorkbook.Path & "\usage.log" For Append As #1
    Print #1, Application.UserName, Now
    Close #1
End Sub

When this procedure is executed, it appends the user name and the current date/time to a text file. The file is stored in the workbook's directory, and is named usage.log. If the text file does not exist, it is created. You can, of course, change the code so the text file is written to a different directory.

NOTE: Keep in mind that this procedure is not executed if the workbook is opened with macros disabled.