VBA Password and Protected Worksheets

[GaryA]

I have a macro enabled workbook with protected worksheets that require the sheet protection be removed each time the macros are run and then added back on completion. I am looking to create a function to do this efficiently whenever any worksheet selected, and instead of putting the code at the beginning and end of each macro.

Also, How would you hide or obfuscate the vba protection password (Cypher) instead of displaying it somewhere in the code? I also find that I have to add GetInfo() to each module, instead of in one place. If you make it public, then it is available to a User in the macro pulldown of the workbook.

Here is a typical example of the code I am using when I only need to run a macro on a single protected sheet.

Sub (UnprotectSheet)
‘ Get the Cypher to unprotect the worksheets
GetInfo
‘Unprotect active worksheet
ActiveSheet.Unprotect Password:=(Cypher) 
‘**********************
‘ Run some VBA on active worksheet
‘**********************
‘Protect active worksheet
ActiveSheet.Protect Password:=(Cypher) 
End Sub

Alternatively, I use the following code when working on all Worksheets:

Sub (UnprotectAll)
‘ Get the Cypher to unprotect the worksheets
GetInfo
‘Unprotect all sheets in workbook
For Each Sh In ActiveWorkbook.Worksheets
Sh.Unprotect Password:=(Cypher)
Next Sh
‘**********************
‘ Run some VBA on multiple sheets
‘**********************
‘Protect all sheets in workbook
GetInfo
For Each Sh In ActiveWorkbook.Worksheets
Sh.Protect Password:=(Cypher)
Next Sh
End Sub

Private Sub GetInfo()
Cypher = "abc123"
End Sub

[Ken Puls]

Hi Gary,

Bad news... you can't hide the password in the code. It's in plain text. You could try and obfuscate it by using some functions, or hide it in the registry, but again, it's plain text there. Honestly, with a little more VBA skill you'll be able to hack any worksheet protection anyway, so I don't even bother with a password when I protect my worksheets. I only do it to prevent accidental damage.

With regards to the protect/unprotect, there is a setting which allows you to protect all worksheets from users, but still allow macros to run on them. The bad news there is that it doesn't persist from session to session so you have to re-establish it every time you open the workbook. Here's an example of something that I pulled from a real live project of my own:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    
    With Application
        .ScreenUpdating = False
        .WindowState = xlMaximized
    End With
    
    ActiveWindow.WindowState = xlMaximized
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .EnableSelection = xlNoRestrictions
            .EnableAutoFilter = True
            .EnableOutlining = True
            If Application.UserName <> "Ken Puls" Then
                .Protect Password:="", userinterfaceonly:=True
            Else
                .Unprotect Password:=""
            End If
        End With
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

You'd put this in the ThisWorkbook module. This will protect every worksheet upon opening (providing macros are enabled), while still letting the users:

• Select any cell on the worksheets (protected or not)
• Use Autofilter
• Use Outlining (no way to enable outlining on protected worksheets via the UI, only code!)

In addition, it:

• Unprotects all worksheets if I open the file (or someone who is smart enough to change their username to Ken Puls)
• Protects all worksheets if anyone else opens the file, but while still allowing macros to run

[GaryA]

Thanks Ken, This code works like a charm!

I would like to try obfuscating the password or hiding it the registry, but this is an area I am unfamiliar with and will need your help.

[Ken Puls]

Hi Gary,

This gets a little difficult, only because in order to obfuscate it, you need to feed it into some kind of routine to do so which... requires starting with the original password. At least you do if you want to save it in the code. And no matter what you do, you're going to need to have a routine to decrypt the password in your workbook. That means that if someone were to carefully follow it, they could reverse engineer it to figure out the algorithm.

Having said all this, if you still want to go ahead with it, the approach I would follow is this:

• Have the routine to generate the obfuscated password in another workbook
• Generate the password there
• Put it and the decryption routine in the workbook you need them in
• Make sure that the name of the decryption routine and password variables are obfuscated too.

Here's an example...

A relatively easy obfuscator that just takes each letter of the password and changes it into it's ASCII numeric equivalent:

Function obfuscate(sPassword As String) As String
'Written By:    Ken Puls (www.excelguru.ca)
'Macro Purpose: Obfuscate a password by changing letters to ASCII equivalents
'Note:  Do not place in same workbook as the decrypter!

    Dim lChar As Long
    
    For lChar = 1 To Len(sPassword)
        obfuscate = obfuscate & Asc(Mid(sPassword, lChar, 1)) & "|"
    Next lChar
    obfuscate = Left(obfuscate, Len(obfuscate) - 1)
End Function

So feeding the phrase "My Password" through this function would return "77|121|32|80|97|115|115|119|111|114|100". Remember, to make it as hard as possible for someone to decrypt what we've done, the encryptor should NOT be in the same workbook as our main code.

Now, the next part we need is a way to decrypt the password for use. This DOES need to be in the workbook where we need to use the password, or we have no way to get it back out. Here's a version that works to decrypt the password above (written legibly so you can understand what it's doing):

Public Function decrypt(s) As String
'Written By:    Ken Puls (www.excelguru.ca)
'Macro Purpose: Decrypt a passowrd encoded in ASCII equivalents    Dim lChar As Long
    Dim ary() As String
    
    'Split password into an array
    ary() = Split(s, "|")
    
    'Convert each character back to normal text
    For lChar = 0 To UBound(ary())
        decrypt = decrypt & Chr(ary(lChar))
    Next lChar
End Function

Of course, putting in a full labelled decryptor would be a little silly, as it let's someone work out what is happening very easily. And while we can't eliminate the fact that they could reverse engineer it, there is no point in making it easy. So if we obfuscate that code, remove the comments and whitespace, we get:

Public Function dp(s) As String
    Dim c As Long
    Dim a() As String
    a() = Split(s, "|")
    For c = 0 To UBound(a())
    dp = dp & Chr(a(c))
    Next c
End Function

Same thing, and feeding "77|121|32|80|97|115|115|119|111|114|100" into it will return "My Password"

Now, to use this, you could modify the protection routine above as follows:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Const p = "77|121|32|80|97|115|115|119|111|114|100"
    
    With Application
        .ScreenUpdating = False
        .WindowState = xlMaximized
    End With
    
    ActiveWindow.WindowState = xlMaximized
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .EnableSelection = xlNoRestrictions
            .EnableAutoFilter = True
            .EnableOutlining = True
            If Application.UserName <> "Ken Puls" Then
                .Protect dp(p), , , , True
            Else
                .Unprotect dp(p)
            End If
        End With
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

So I've added a constant to the top of the routine that has the "encrypted" password, then call the obfuscated decryption routine to pass that to the protect/unprotect routines. I've also removed the Password:= arguments from the code, as that brings attention to what it is.

Some other things I would suggest...

• Put the decryptor in a separate module from your other code and place the line "Option Private Module" at the very top. This will allow the decryption routine to be called from within the modules, but will hide it from the macro run dialog.
• Move the Const line out of the Workbook_Open routine, and place it under the Option lines in the new module. You'll also want to mark it as "Public Const" so that it can be seen from other modules. The benefit of this is that it isn't close to your original protection routine, which makes it harder to find.

So this will help you prevent the casual coder from guessing the password, but someone determined... well... there's nothing you can do to stop them.

With regards to the registry... unless you are distributing your workbook with a setup routine in another workbook, I would avoid the registry. It sounds great in theory, but you need to have the password in the file in order to write it to the registry when the user opens the workbook so that you can retrieve it.

On the other hand, if you're trying to prevent anyone from unprotecting it, I suppose this might be one way that you could. Have another workbook that writes the password key to the registry, and have your main workbook retrieve the key from the registry. If that other workbook has never been opened, then they won't have the key at all. The only issue is that you would have there however, is that it won't be able to re-run the "UserInterfaceOnly=True" each time as you wouldn't have the password to set it. Does that make sense?

[GaryA]

Hi Ken,

I finally got around to looking at this. You have created an outstanding tutorial here. It all makes sense. I am very impressed. Thanks so much!

Workbook attached with your examples.

Gary

[Ken Puls]

Great Gary, glad to hear it was helpful.

I wonder if this should be converted into an article... merely for the sake of academics...

[GaryA]

Great idea!