Imagine an Excel workbook with data that is very important to you, stored on a shared company network drive that a lot of people have access to. One not at all perfect morning, you open this file and find that someone inside has played al-Qaeda: the formulas are broken, the data is erased, the design is broken. Since you are an experienced user, then, of course, you had a backup copy of this important document (was it really?) and you will restore the data, but purely out of sports interest, I would still like to know – WHO DID IT ?!
Let’s try to solve this problem. So we need:
- record on a separate (hidden) sheet the name of the user who opened the file;
- fix the date and time of opening and closing the file in the same place;
- make it as difficult as possible to hack or bypass such protection.
Go…
Stage 1. Create a “Log”
Let’s add a new sheet to our book, where information about all users will be recorded and call it, for example, Log. On it we will create a simple header for the future visitor log:
Stage 2. I/O fix macros
Now let’s add macros to write to the sheet Log date-time and usernames when opening and closing the workbook. To do this, you need to open the Visual Basic Editor using the shortcut Alt + F11 or using the Visual Basic button on the tab Developer and find the panel in the upper left corner Project (if it is not displayed, then you can turn it on with the keyboard shortcut Ctrl + R):
Double click to open the module ThisWorkbook and paste a couple of our macros there to handle book open and close events:
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'look for the last busy line in the logs lastrow = Worksheets("Log").Range("A60000").End(xlUp).Row 'put the date-time of file exit If lastrow>1 Then Worksheets("Log").Cells(lastrow, 3) = Now 'Save before exiting ActiveWorkbook.Save End Sub Private Sub Workbook_Open() 'Look for the last busy line in the logs lastrow = Worksheets("Log").Range("A60000" ).End(xlUp).Row 'put the username and login date-time into the file Worksheets("Log").Cells(lastrow + 1, 1) = Environ("USERNAME") Worksheets("Log").Cells( lastrow + 1, 2) = Now End Sub As a first approximation, everything should already work. Try opening-closing this file a couple of times and make sure the sheet Log gets your username (Windows login) and date-time:
Stage 3. Improving reliability
It would be possible to hide the sheet Log and stop there, but there is one “but”: if the user who opens our book has macros enabled by default, or he allows them himself by clicking on the button in the warning window Enable content, then everything is fine:
But what if the user doesn’t allow macros or they are disabled by default? Then our tracking macros will not be executed and the name and date will not be fixed 🙁 How can we force the user to allow the use of macros?
To get around this problem, we use a little tactical trick. Add another blank sheet to our book, name it A warning and paste the following text on it:
The bottom line is to hide all sheets in the book except this one by default, and display worksheets with data using a special macro. If the user does not allow macros, then he will see only one warning sheet in the workbook. If macros are enabled, then our book open event macro will hide the alert sheet and display the data sheets. To prevent the user from displaying them himself, we use super-hiding instead of the usual hiding of sheets (the xlSheetVeryHidden parameter instead of the usual False).
To implement everything described, we will slightly change our procedures in the module ThisWorkbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'look for the last busy line in the logs lastrow = Worksheets("Log").Range("A60000").End(xlUp).Row 'put the date-time of file exit If lastrow > 1 Then Worksheets("Log").Cells(lastrow, 3) = Now 'hide all sheets except sheet WARNING Worksheets("Warning").Visible = True For Each sh In ActiveWorkbook.Worksheets If sh.Name = "Warning" Then sh .Visible = True Else sh.Visible = xlSheetVeryHidden End If Next sh 'save before exiting ActiveWorkbook.Save End Sub Private Sub Workbook_Open() 'look for the last busy line in the logs lastrow = Worksheets("Log").Range("A60000") .End(xlUp).Row 'put the username and login date-time into the file Worksheets("Log").Cells(lastrow + 1, 1) = Environ("USERNAME") Worksheets("Log").Cells(lastrow + 1, 2) = Now 'display all sheets For Each sh In ActiveWorkbook.Worksheets sh.Visible = True Next sh 'hide sheets WARNING and LOG Worksheets("Warning").Visible = xlSheetVe ryHidden Worksheets("Log").Visible = xlSheetVeryHidden End Sub To view hidden Log open the VisualBasic editor (Alt + F11), select a sheet in the panel Project and change its visibility in the panel Properties, using the property Visible:
If users are so advanced that they know about super-hidden sheets and can display them through the Visual Basic editor or disrupt the work of our macros, then you can additionally set a password to view and change macros. To do this, right-click on the file name in the panel Project (line VBAProject (blackbox.xls)), select command VBA Project Properties and turn on the checkbox Lock project for viewing and set the password on the tab Protection:
Now no one will go unpunished. Big brother is watching you! 😉
- Super Hidden Leaf
- Selective display of sheets to individual users
- 4 Ways to Protect Data in Microsoft Excel