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 […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
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