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 click "Visual Basic" button to open the Visual Basic Editor or press shortcut keys Alt+F11
- Double click "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.
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies [โฆ]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The [โฆ]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The [โฆ]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower [โฆ]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible [โฆ]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above [โฆ]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two [โฆ]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โฆ]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder [โฆ]
4 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.
English
Spanish
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 and Sevgili Nilhan,
Both of your question and answer did alot for me, thank you very much.
Regards