Home

Spreadsheet protection allowing hiding/showing details

%3CLINGO-SUB%20id%3D%22lingo-sub-801839%22%20slang%3D%22en-US%22%3ESpreadsheet%20protection%20allowing%20hiding%2Fshowing%20details%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801839%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20protect%20a%20spreadsheet%2C%20in%20which%20columns%20and%20rows%20are%20grouped%20in%20different%20levels.%20I%20would%20like%20to%20allow%20users%20to%20be%20able%20to%20hide%20or%20show%20the%20grouped%20columns%20and%20rows%2C%20although%20they%20cannot%20modify%20the%20data%2Fformulas%20inside%20the%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-801839%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-801971%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20protection%20allowing%20hiding%2Fshowing%20details%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391267%22%20target%3D%22_blank%22%3E%40gianpierobrozzi%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20there%20is%20no%20built-in%20option%20to%20allow%20that!%3C%2FP%3E%3CP%3EBut%2C%20if%20you're%20familiar%20with%20VBA%2C%20you%20can%20use%20this%20code%20in%20%3CSTRONG%3EThisWorkbook%3C%2FSTRONG%3Ecode%20module%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A'Updated%20By%20Haytham%20Amairah%20in%208%2F13%2F2019%0A'EnableOutlining%20in%20a%20protected%20worksheet%0A'Applied%20only%20to%20a%20specific%20sheet%20which%20is%20(Sheet1)%0A%20%20%20%20Dim%20xWs%20As%20Worksheet%0A%20%20%20%20Set%20xWs%20%3D%20Application.Worksheets(%22Sheet1%22)%0A%20%20%20%20Dim%20myPassword%20As%20String%0A%20%20%20%20myPassword%20%3D%20%22abc123%22%0A%20%20%20%20xWs.Unprotect%20password%3A%3DmyPassword%0A%20%20%20%20xWs.Protect%20password%3A%3DmyPassword%2C%20Userinterfaceonly%3A%3DTrue%0A%20%20%20%20xWs.EnableOutlining%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20some%20notes%20about%20the%20code%3A%3C%2FP%3E%3CUL%3E%3CLI%3EIt%20will%20run%20each%20time%20you%20open%20the%20workbook%3C%2FLI%3E%3CLI%3EIt%20will%20unprotect%20the%20worksheet%2C%20enable%20the%20Outlining%20option%2C%20and%20then%20protect%20it%20again%3C%2FLI%3E%3CLI%3EYou%20have%20to%20tell%20the%20code%20the%20password%20as%20above%20(abc123)%3C%2FLI%3E%3CLI%3EIt's%20applied%20only%20to%20a%20specific%20sheet%20which%20is%20(Sheet1)%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThis%20code%20is%20a%20modified%20version%20of%20the%20code%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F1723-excel-group-ungroup-in-protected-sheet.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you're%20not%20familiar%20with%20VBA%2C%20check%20out%20the%20link%20above%20to%20learn%20how%20to%20install%20the%20code%20in%20the%20workbook%2C%20but%20replace%20that%20code%20in%20the%20link%20with%20the%20code%20above%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EDon't%20worry%20about%20providing%20the%20password%20in%20the%20code%20as%20you%20can%20protect%20the%20entire%20VBA%20project%20so%20that%20no%20one%20can%20open%20it%20and%20see%20the%20code%2C%20so%20watch%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DKbjIu8I3mi4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3Eto%20learn%20how%20to%20protect%20the%20project%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20want%20to%20apply%20that%20to%20all%20worksheets%20in%20the%20workbook%2C%20please%20use%20this%20one%20instead%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A'Updated%20By%20Haytham%20Amairah%20in%208%2F13%2F2019%0A'EnableOutlining%20in%20a%20protected%20worksheet%0A'Applied%20to%20all%20worksheets%20in%20the%20workbook%0A%0ADim%20xWs%20As%20Worksheet%0ADim%20myPassword%20As%20String%0AmyPassword%20%3D%20%22abc123%22%0A%0A%20%20%20%20For%20Each%20xWs%20In%20Me.Worksheets%0A%20%20%20%20%20%20%20%20xWs.Unprotect%20password%3A%3DmyPassword%0A%20%20%20%20%20%20%20%20xWs.Protect%20password%3A%3DmyPassword%2C%20Userinterfaceonly%3A%3DTrue%0A%20%20%20%20%20%20%20%20xWs.EnableOutlining%20%3D%20True%0A%20%20%20%20Next%20xWs%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
gianpierobrozzi
Occasional Visitor

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!

1 Reply

@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 link
  • 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 video 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

Related Conversations