Aug 12 2019 10:01 PM
Hello,
I would like to protect a spreadsheet, in which columns and rows are grouped in different levels. I would like to allow users to be able to hide or show the grouped columns and rows, although they cannot modify the data/formulas inside the cells.
How can I do that?
Thank you!
Aug 12 2019 11:27 PM - edited Aug 12 2019 11:31 PM
Hi,
In fact, there is no built-in option to allow that!
But, if you're familiar with VBA, you can use this code in ThisWorkbook code module:
Private Sub Workbook_Open()
'Updated By Haytham Amairah in 8/13/2019
'EnableOutlining in a protected worksheet
'Applied only to a specific sheet which is (Sheet1)
Dim xWs As Worksheet
Set xWs = Application.Worksheets("Sheet1")
Dim myPassword As String
myPassword = "abc123"
xWs.Unprotect password:=myPassword
xWs.Protect password:=myPassword, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub
Here are some notes about the code:
If you want to apply that to all worksheets in the workbook, please use this one instead:
Private Sub Workbook_Open()
'Updated By Haytham Amairah in 8/13/2019
'EnableOutlining in a protected worksheet
'Applied to all worksheets in the workbook
Dim xWs As Worksheet
Dim myPassword As String
myPassword = "abc123"
For Each xWs In Me.Worksheets
xWs.Unprotect password:=myPassword
xWs.Protect password:=myPassword, Userinterfaceonly:=True
xWs.EnableOutlining = True
Next xWs
End Sub
Hope that helps
Mar 09 2023 07:34 AM
@Haytham Amairah perfect. just what I needed.