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!
Hello 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