Workbook log (vba)
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 SubWhere to copy code?
- Launch Visual Basic Editor or press Alt+F11
- Double click "This workbook" in project explorer
- Paste code into code window
- Exit to Excel
Download excel *.xlsm file
Related posts:
Copy each sheet in active workbook to new workbooks
Copy selected rows (checkboxes) (2/2)
List all tables and corresponding headers in a workbook (vba)
Recommended categories
Hi Oscar,
Is there a way to include the name of the person (from computer name or user name, which appears in the excel options)in the code?
This would be a nice option to have when there are multiple users.
Regards,
Nilhan
Nilhan,
Sure!
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 Worksheets("Log").Range("C" & Lrow).Value = Environ("USERNAME") Worksheets("Log").Range("D" & Lrow).Value = Environ("COMPUTERNAME") 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 Worksheets("Log").Range("C" & Lrow).Value = Environ("USERNAME") Worksheets("Log").Range("D" & Lrow).Value = Environ("COMPUTERNAME") End Sub