Forum Discussion
gianpierobrozzi
Aug 12, 2019Copper Contributor
Spreadsheet protection allowing hiding/showing details
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...
Haytham Amairah
Aug 12, 2019Silver Contributor
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:
- It will run each time you open the workbook
- It will unprotect the worksheet, enable the Outlining option, and then protect it again
- You have to tell the code the password as above (abc123)
- It's applied only to a specific sheet which is (Sheet1)
- This code is a modified version of the code in this https://www.extendoffice.com/documents/excel/1723-excel-group-ungroup-in-protected-sheet.html
- If you're not familiar with VBA, check out the link above to learn how to install the code in the workbook, but replace that code in the link with the code above
- Don't worry about providing the password in the code as you can protect the entire VBA project so that no one can open it and see the code, so watch this https://www.youtube.com/watch?v=KbjIu8I3mi4 to learn how to protect the project
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
jeffrey-nbpower
Mar 09, 2023Copper Contributor
Haytham Amairah perfect. just what I needed.