Excel: Protect All Sheets Part 2

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.