Forum Discussion
Using Group-Ungroup in a protected Excel sheet
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 SubHansVogelaar thanks a lot! Works fine!
- You are a legend. Thanks
