Using Group and Ungroup on multiple sheet when password protected

Hi all,

I'm trying to make this code work for multiple sheets but everything I've tried won't work. Here is the code so far:

Private Sub Workbook_Open()
With Sheet1
.Unprotect Password:="Secret"
.EnableOutlining = True
.Protect Password:="Secret", UserInterfaceOnly:=True
End With
End Sub

Found the answer...(problem)!!! I had 2 different passwords. Once I corrected this and used the same password on both sheets, the code below worked!!!!


Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
With wks
.Unprotect Password:="Secret"
.EnableOutlining = True
.Protect Password:="Secret", UserInterfaceOnly:=True
End With
Next wks
End Sub


This code will be executed automatically each time the workbook is opened, and protect all sheets. It will also allow users to "Group & Ungroup"

This is actually cleaner:

You need VBA for this, and the end user will need to allow macros for this to work.
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.


Copy the following code into the module that appears:


Private Sub Workbook_Open()
For Each Sheet In Worksheets
Sheet.Unprotect Password:="Secret"
Sheet.EnableOutlining = True
Sheet.Protect Password:="Secret", UserInterfaceOnly:=True
Next
End Sub


This code will be executed automatically each time the workbook is opened, and protect all sheets.
Make sure password is the same for all sheets!!!!
----------------------------------------------------