Protected Sheet Permissions - Expanding grouped cells

[Marian DiCicco]

Hello Masters of Happiness, I have learned tons on this site without ever asking a question, but today I need help!!

I have a protected worksheet, and users are to access certain cells. Those columns that are most relevant are displayed, while less relevant columns are grouped and collapsed. I need the user to be able to expand and view the collapsed/hidden columns.

Can I enable such a permission in a protected worksheet/workbook? If so, how? I have experimented with the protect worksheet options, but no luck there. . . .

 

[Hellsteeth]

Does this other post address what you are looking for? https://smallbusiness.chron.com/expand-groups-protected-worksheet-excel-2007-61513.html

 

[Marian DiCicco]

I will try it - my first thought is that if I unlock the cells for expansion, then the content can be edited also - I wonder if I have to leave the cells 'unprotected' to enable the expansion of the grouped/collapsed columns?

 

[Marian DiCicco]

Thank you , Hellsteeth, but rats - this does not work(!) - don't know if it's version difference... I am in Excel 2016. Anyone?

 

[Hellsteeth]

Sorry that was my one shot answer, hopefully someone else on here will know more... Good luck.

 

[ZVI]

Hi,
Put this code into ThisWorkbook module

Private Sub Workbook_Open()
  Const PWD = "123"  '<-- Put password here
  Dim Sh As Worksheet
  For Each Sh In Me.Worksheets
    Sh.Protect PWD, UserInterfaceOnly:=True
    Sh.EnableOutlining = True
  Next
End Sub

Replace "123" with real password for the sheets.
Then save workbook in macro enabled format (XLSM or XLSB), close and reopen workbook, allow macros.
Instead of workbook reloading you can run Workbook_Open manually as well.