Hide VBA worksheet PW in VBA code without Project protection.

[StuartWhi]

Hi All,

I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas…

Is there a way I can encrypt the password within VBA so it’s not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.

P.S. I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
P.P.S. I have read and understand many post’s about protecting worksheets with VBA and VBA Project protection but I'm not able to find one covering my above issue.

Thanks in advance.

Stuart.

 

[Jerry Sullivan]

Hi Stuart,

One option to consider would be set the Protect Method's UserInterfaceOnly parameter to True.
This protects the worksheet during User Interface but allows macros to make changes without the need for the macro to temporarily unprotect the sheet.

You could setup this protection using a macro stored in a location that isn't accessible to others.

Sub Set_UI_Only_Password()
'---Place this in your Personal.xlsb file or Add-In
    Const sMyPassword As String = "fKj@49p"

    
    ActiveSheet.Protect Password:=sMyPassword, [B][COLOR="#0000CD"]UserInterfaceOnly:=True[/COLOR][/B], _
        AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub

 

[RoryA]

The short answer is no you can't encrypt part of a VBA project. You could use functions to obfuscate the password but they would have to be part of the project so that is unlikely to help. Of course, as you note, if the other staff member can code, your protection isn't worth much anyway!

 

[StuartWhi]

Hi JS411 and RoryA,

Thanks for your input and ideas.

Regards,

Stuart.