Forum Discussion
Using Group-Ungroup in a protected Excel sheet
You have to create the grouping/outline while the worksheet is unprotected.
You have to protect the sheet in a special way when the workbook is opened.
Activate the Visual Basic Editor (Option+F11).
Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left.
Copy this code into the ThisWorkbook module:
Private Sub Workbook_Open()
With Me.Worksheets("Sheet1")
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End SubReplace Sheet1 with the name of the relevant sheet, and replace Secret with the password that you used to protect the sheet (use "" if you didn't specify a password).
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
If you want to do this for all worksheets in the workbook:
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In Me.Worksheets With ws .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With Next ws End SubIf you want to apply it to several but not all worksheets, use something like this:
Private Sub Workbook_Open() Dim ws As Worksheet ' Modify the list of sheet names as needed For Each ws In Me.Worksheets(Array("Sheet2", "Sheet4", "Sheet6")) With ws .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With Next ws End SubHansVogelaar thanks for this!
I am using a workbook with tens of tabs. I would like to apply the code to a selection of tabs without having to list them all in the code because 1)many 2)I might have to add and delete tabs and hence want to avoid having to hardcode them in the code. Is it possible to use a list of tabs without having to hardcode them individually in de code?
ps: I have a tab with a list of all tabs, and I have created a named range with the tabs I would like to protect. It if might help…Let's say that you named the range with the tabs to protect ToProtect. The code could then look like this:
Private Sub Workbook_Open() Dim rng As Range For Each rng In Range("ToProtect") With Worksheets(rng.Value) .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With Next rng End Sub
- You are a legend. Thanks
