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.
One Reply to “Excel: Protect All Sheets Part 2”
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.