Results 1 to 7 of 7

Thread: VBA Password and Protected Worksheets

  1. #1

    VBA Password and Protected Worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Code:
    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:

    Code:
    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
    Last edited by GaryA; 2012-04-26 at 04:36 PM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    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:

    Code:
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    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:
    Code:
    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):
    Code:
    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:
    Code:
    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:
    Code:
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Great Gary, glad to hear it was helpful.

    I wonder if this should be converted into an article... merely for the sake of academics...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Great idea!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •