Hello Excellers, welcome back to another #Excel #MacroMonday blog post in my 2020 all things Macro series. Do you need to know how many times a workbook has been opened?. Would you like to know how many times your Excel workbook has been opened?. We could also log who opened it and when right?. Handy to know?. Why not?. Here is an easy Excel VBA solution to this problem.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
How Does The Macro Work?.
This macro if a really simple one. It will count the number of times your workbook is opened. For each time the workbook is opened the count is increased by one. and the workbook is saved. You can choose where to maintain or save this count. I have decided to save it simply in cell A1 of a sheet named “count”.
Starting The Macro.
This type of code is going to be counting how many times the workbook has opened. Therefore in order to store the VBA code within the workbook, you will need to double click on the ‘This Workbook’ are of the VBA Explorer and Select Workbook from the left dropdown menu, then select Open from the right drop-down menu.
This places the code in the module for this workbook.
Once you choose this selection Excel starts the code for you with the following automatically displayed
[stextbox id=’info’]
Sub Workbook_Open()
End Sub
[/stextbox]
Using The With Statement.
We use the With statement is used which will allow access to all the properties and methods of a mentioned object. The syntax of the With statement is below.
With [OBJECT]
[Code… What needs to be done?]
End With
All we need to do supply which VBA object we are referring to first then close the With statement with End With. In this example today the Object is Worksheet 2. The code or what needs to be done is increasing the count in cell A1 by one every time the workbook is opened.
[stextbox id=’info’]
With Worksheets(“Sheet2”)
.Range(“A1”).Value = .Range(“A1”).Value + 1
End With
Me.Save
[/stextbox]
Ending The Macro.
The VBA code ends with the “End Sub" piece of code. This was already entered into the module for us when started the type the name of the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Hiding The Worksheet With Count Of Workbook Opening.
Finally, if you want the value you have created counting the number of times the workbook has been opened then you can hide (as best we can) the worksheet by setting the properties of it to being VERY HIDDEN. Normally when you want to hide an Excel worksheet you can right-click on the worksheet tab and select Hide. This does hide the worksheet but any user can come along and right-click any worksheet tab and select one of the hidden tabs.
We can, however, make the worksheet ‘VERY’ hidden. We do this by using the Visual Basic Project Explorer to select the Excel worksheet we want to hide.
- Click the worksheet which contains the count of how many times our workbook has been opened.
- In the Visible Properties section, select 2- xlSheetVeryHidden.
The only way that a user can now make this worksheet visible or unhide it is to use the Visual Basic Project Explorer. So, for most basic users they would look, but more advanced users may take a look around and find this ‘VERY’ hidden worksheet.
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
