Spreadsheet protection allowing hiding/showing details

gianpierobrozzi

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