Forum Discussion

gianpierobrozzi's avatar
gianpierobrozzi
Copper Contributor
Aug 12, 2019

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 they cannot modify the data/formulas inside the cells.

 

How can I do that?

 

Thank you!

excel
Excel on Mac
office 365

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 12, 2019

    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