Help with Workbook and WorkSheet Protect/Un-Protect Macro

[WWII_Buff]

Hello all!

I need help with a macro to password protect and unprotect several sheets at once - and prompt the user when an incorrect password is given. Also, when each sheet is protected I would like user only to have access to 1. group/ungroup, 2. edit column widths, 3. access unlocked cell, 4. select but not edit locked cells.

I haven't been able to cobble anything together that give me all requirements in 1 macro. I wish this stuff came easy to me

Thanks for your help.

 

[Roderick_E]

Have you thought where you're going to store this password? It is not fail safe for someone looking into the code to find this password. You could write a bit of obfuscation code but ultimately someone could figure it out. So, how secure does this need to be?

Otherwise, just set all the sheets protections up like you want them; allowing for access to groups and such as you said. Protect all sheets and then add a button to allow the user to unprotect selected sheets. Might need a userform that allows for listbox selection of sheets.

 

[WWII_Buff]

Hi Roderick! Thanks for your response.

So here is what I have so far - added into the module. However, I can't get the .EnableOutlining to work. I know it's some how should reside in the workbook, but does that mean I need to of the same macro twice? I am not to worried about the users finding the password as much as I don't want anyone "mistakenly" overwriting the formulas.

Sub Protect_sheets() 
Dim ws As Worksheet
Dim pwd As String


pwd = "reporting"
For Each ws In Worksheets
    ws.Protect Password:=pwd, AllowFormattingColumns:=True
        [COLOR=#0000cd][B].EnableOutlining = True[/B][/COLOR]
Next ws
        
End Sub

Sub UnProtect_sheets()
    Dim ws As Worksheet
    Dim MyPassword As String
    MyPassword = InputBox("Enter Password.", "Password Entry")


    If MyPassword = "pass" Then
        For Each ws In Worksheets
            ws.UnProtect Password:="reporting"
        Next ws
        
        MsgBox "All sheets are now unprotected.", vbCritical, "Sheets Unprotected"
    Else
        MsgBox "You entered an incorrect password.", , "Password Failed"
        Exit Sub
    End If
End Sub

 

[Roderick_E]

Take a look at this for the enablingoutining issue:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-enableoutlining-property-excel

It looks like you already have the code to do what you requested except it looks like you have 2 different passwords?

 

[WWII_Buff]

Take a look at this for the enablingoutining issue:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-enableoutlining-property-excel

It looks like you already have the code to do what you requested except it looks like you have 2 different passwords?

Nope - just the one. Still work in progress. I'll gave that link a look.

Thanks Rod!