Excel: How to allow grouping/outlining on multiple worksheets with protection on?

Question

I am trying to allow grouping/outlining on multiple worksheets that have protection turned on. For some reason excel doesn't have a simple option box to do this when protecting- so I'm using this macro code:

Sub group()
ActiveSheet.EnableOutlining = True'
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
End Sub

I set it up as an auto-run macro when the workbook is opened. The issue I have is that I want it to apply to all sheets, not just the active sheet. The code aboves works on the active sheet, but I still have to manually run the macro on the other sheets to allow the outlining to work.

I also need some flexibility in that sometimes worksheets will be added or deleted, and I want the code to be flexible so that it always affects all worksheets without me having to name every worksheet in the code.

Is this possible?

Answer 1

I think this is what you want:

Option Explicit
Sub group()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    With ws
        .EnableOutlining = True '
        .Protect Contents:=True, UserInterfaceOnly:=True
    End With
End Sub

Answer 2

The could should be:

Option Explicit
Sub group()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        .Protect Contents:=True, UserInterfaceOnly:=True
        .EnableOutlining = True 'add after adding protection to the sheet
    End With
Next ws 'you need the next rule for the "For" routine.
End Sub

Greetings Paul