This post demonstrates how to automatically create log entries when a workbook opens or closes.

VBA code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Lrow As Single
Lrow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
If Worksheets("Log").Range("A" & Lrow - 1).Value = "Close workbook" Then Lrow = Lrow - 1
Worksheets("Log").Range("A" & Lrow).Value = "Close workbook"
Worksheets("Log").Range("B" & Lrow).Value = Now
End Sub
Private Sub Workbook_Open()
Dim Lrow As Single
Lrow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Log").Range("A" & Lrow).Value = "Open workbook"
Worksheets("Log").Range("B" & Lrow).Value = Now
End Sub

Where to copy code?

  1. Launch Visual Basic Editor or press Alt+F11
  2. Double click "This workbook" in project explorer
  3. Paste code into code window
  4. Exit to Excel

Download excel *.xlsm file

Log.xlsm