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"
   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.

One Reply to “Excel: Protect All Sheets Part 2”

  1. I appreciated your posts about setting up TreeView in Access. I am interested in a similar capability that would take a product Bill of Material (BOM) – which if you aren’t familiar is the organized structure of how a physical product is put together, the components, the fasteners, etc. As you can imagine, there are some parts which are used in different products – nuts and bolts for example. These also may be used in multiple different assemblies, so the Tree location cannot be fixed, this is a classic one-to-many relationship. Do you have any ideas on how to address this? It seems that the standard TreeView doesn’t handle this situation.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.