Password Protect Active Sheets Issue

[r0bism123]

Hi Folks!

I am hoping someone can help me out. I have a workbook with 84 tabs which all tabs need to be password protected. I came up with the code below but then I realized that several of the tabs require various other formatting requirements other than AllowFormmattingRows:=True.

For example, I have some tabs that need AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowEditObjects:=True and different combinations thereof. Is it possible to assign different formats to individual tabs versus assigning the same formats to all tabs in the workbook?

I've been messing around with it tonight and can't get it there. I appreciate the help!

Sub protect_all_sheets()
On Error GoTo fixpw
pass = InputBox("password")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:=pass, AllowFormattingRows:=True
Next
Exit Sub
fixpw: MsgBox "There is a problem - check your password, capslock, etc."
End Sub

 

[Fluff]

You could use a select case statement, like

Sub protect_all_sheets()
   Dim Ws As Worksheet
   On Error GoTo fixpw
   Pass = InputBox("password")
   For Each Ws In ActiveWorkbook.Worksheets
      Select Case Ws.Name
         Case "Report", "Master"
            Ws.Protect Password:=Pass, AllowFormattingRows:=True
         Case "List", "Archive"
            'do something
         Case Else
            ' all other sheets
      End Select
   Next Ws
Exit Sub
fixpw:
   MsgBox "There is a problem - check your password, capslock, etc."
End Sub

 

[r0bism123]

Thank you for the help here, Fluff. I'll give this a try.

I do a few a questions:
- How do I know which Case corresponds to which tab? Does the code execute the formatting in order of the tabs?
- I have 68 tabs that will have the same formatting. Is there a way to include all 68 into one Case or do I need do separate lines for each tab?

 

[Fluff]

The tab names are in blue

Case "[COLOR=#0000ff]Report[/COLOR]", "[COLOR=#0000ff]Master[/COLOR]"

In my Example sheets Report & Master would allow formatting of rows.
Sheets List & Archive would allow something different & all other sheets would allow something else again.

 

[r0bism123]

I understand better now. I will give this a try. I may need some additional help. I really appreciate it!

 

[Fluff]

You're welcome.
If you get stuck just shout.

 

[r0bism123]

Fluff,

This worked perfectly! Thank you for your help. I really appreciate it.

 

[r0bism123]

Fluff,

I spoke too soon. It appeared that Excel was executing the code but it turns out that the conditions I want applied on the tabs were not being applied. Can you take a look at the code below and let me know if I missed something.

I first unlock the tabs executing this code:

Sub unprotect_all_sheets()
On Error GoTo Fixpw
unpass = InputBox("password")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
Fixpw: MsgBox "There is a problem - check your password, capslock, etc."
End Sub

Then I tried this:

Sub protect_all_sheets()
   Dim Ws As Worksheet
   On Error GoTo fixpw
   Pass = InputBox("password")
   For Each Ws In ActiveWorkbook.Worksheets
      Select Case Ws.Name
         Case "Control"
            Ws.Protect Password:=Pass
         Case "Narrative"
            Ws.Protect Password:=Pass, AllowFormattingRows:=True
         Case "Rent Schedule"
            Ws.Protect Password:=Pass, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, DrawingObjects:=True, Scenarios:=False
         Case Else
            Ws.Protect Password:=Pass, DrawingObjects:=True
      End Select
   Next Ws
Exit Sub
fixpw:
   MsgBox "There is a problem - check your password, capslock, etc."
End Sub

 

[Fluff]

In what way are the conditions not being applied.
If you unlock the "Narrative" sheet & then go to protect it again is the FormatRows box checked?

 

[r0bism123]

Hi Fluff,

I think I figured out my issue but it begs the question of another - maybe you can help.

I misunderstood how DrawingObjects:=True works. The intended result was to have the "Edit Objects" box checked on the tab protection box so that the user can still add comments on the tab and also save screen shots of other files into the workbook on the separate tabs.

Do you know how I would get the "Edit Objects" box checked when this code is executed? I think this will resolve the issue.