Today we will focus on three simple additions to our code from last time
- Only run code for a specific user (myself)
- Prompt the user
- Add a simple Password to the protection
So we start out in VBE, where we add the check by using a call to the Environ function, passing in the variable “UserName”. The Environ function will then return the username of the user currently logged into Windows. If that user is not me, I simple exit the sub, and don’t run the rest of the code.
If Environ("username") <> "AndersEbro" Then Exit Sub
On top of this, I go to the top of the module and add an option:
Option Compare Text
Secondly, we add a prompt for the user to confirm whether they wish to enable protection:
If MsgBox("Protect sheets?", vbYesNo + vbQuestion) = vbYes Then
Finally we add a simple password. Now I do mean simple, because there really is little point to choosing a super complex password here as these passwords are easily removed. So we change the protect line by adding the password, and the final code then becomes:
Private Sub Workbook_BeforeClose(Cancel As Boolean) If Environ("username") <> "AndersEbro" Then Exit Sub If MsgBox("Protect sheets?", vbYesNo + vbQuestion) = vbYes Then Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect "Demo" Next End If End Sub
Just like last time, I’ve also done all the steps in a video if your prefer to watch it that way.