How to log when a workbook is opened and closed [VBA]
This article demonstrates how to automatically create log entries when a workbook opens or closes using event code. Column A contains the action, Open or Close, column B contains the date and time.
Column C contains the user name and column D contains the computer name. Do not paste the code below to a regular module, that won't work. There are detailed instructions below.
VBA code
'This event code begins right before the user closes the workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Dimension variable and declare data type
Dim Lrow As Single
'Save the row of the first empty cell in column A to variable Lrow.
Lrow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
'Check if cell above equals text value "Close Workbook",
'if true then withdraw value in Lrow with 1 and then save the result to Lrow
If Worksheets("Log").Range("A" & Lrow - 1).Value = "Close workbook" Then Lrow = Lrow - 1
'Save text value "Close Workbook" to cell
Worksheets("Log").Range("A" & Lrow).Value = "Close workbook"
'Save date and time to the corresponding cell in column B.
Worksheets("Log").Range("B" & Lrow).Value = Now
End Sub
'This event code starts when the workbook is opened.
Private Sub Workbook_Open()
'Dimension variable and declare data type
Dim Lrow As Single
'Save the row of the first empty cell in column A to variable Lrow.
Lrow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
'Save text value "Open Workbook" to the first empty cell
Worksheets("Log").Range("A" & Lrow).Value = "Open workbook"
Save date and time to the corresponding cell in column B.
Worksheets("Log").Range("B" & Lrow).Value = Now
End Sub
Where to copy code?
- Go to tab "Developer" on the ribbon and press with left mouse button on "Visual Basic" button to open the Visual Basic Editor or press shortcut keys Alt+F11
- Double press with left mouse button on "This workbook" in the project explorer.
- Paste code to workbook module.
- Return to Excel.
- Save your workbook as an *.xslm file in order to save the code as well.
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
5 Responses to “How to log when a workbook is opened and closed [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 SubHello Oscar, is it possible to put this VBA code into a spreadsheet and then get the results to appear along with the file name on a different spreadsheet?
Many thanks
Dale
Hi Oscar not sure if you answered Dale's question but wondering if the results can appear in a separate file
Hi Oscar and Sevgili Nilhan,
Both of your question and answer did alot for me, thank you very much.
Regards