enable group / ungroup in protected sheets

[niklas]

Hi - I am trying to enable group / ungroup in a few sheets that are protected. I do want the users to be able to name the sheets themselves so I cannot hard code the sheet names. I tried this vba to test out and the functionality seems to work.

So my question is - how would I change the code so that I can include say all protected sheets or perhaps from / to sheet. Or said differently - I would like the code to be more dynamic than having to define the names of each tab as the names might change

<< Use CODE -tags >>
​

Private Sub Workbook_Open()
   For Each wks In ThisWorkbook.Worksheets(Array( _
   "BBUS"))
        With wks
            .Unprotect "pw"
            .EnableOutlining = True
            .Protect "pw", contents:=True, userInterfaceOnly:=True
        End With
    Next
End Sub

 

[chirayu]

Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
'your code here for unlocking etc and enabling stuff
Next ws

 

[niklas]

Thank so much. I included the code and it worked until I changed the name of the workbook (at least that what I thought) . Now it does not work anymore. Can anyone see what I am doing wrong - thanks as always Nikals

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect "pw"
.EnableOutlining = True
.Protect "pw", contents:=True, userInterfaceOnly:=True
End With
Next ws
End Sub

 

[chirayu]

Doesn't seem to be anything wrong with the code.

Do any sheets have a different password? If so then code will error while unprotecting
Are any sheets hidden? If so code will error as VBA cannot work on hidden sheets

 

[niklas]

I think I figured it our at least partly. I had enabled macro in the settings and when I changed back to disable with notifications then it started to work again. Perhaps having the macro enabled as default made the macro not execute at startup. Seems a bit odd but it definitely works when I disable with notification (and then enable )