Expand/Collapse in Worksheet not protected by VBA

[gubertu]

Hi all,

I need to protect a sheet and let the user to expand and collapse rows and columns.

I also need to save this WB in .xlsx (not in .xlsm) so I cannot use macros (as far as I know).

Is there a way to protect the sheet by the option Review/Protect sheet and let the user expand and collapse (same as EnableOutlining = True in VBA)?

Appreciate your help!

Thanks

 

[bobsan42]

Yes, there is. If you do not plan to protect/unprotect frequently you wouldn't need a macro so no problem to stay XLSX.
Set up the outlining as you need it. Protect the sheet as you like with all necessary settings.
While the sheet is active go to VB Editor (Alt+F11), then go to Immediate window (Ctrl+G), type these two lines and press Enter after each of them, then save the file:

ActiveSheet.EnableOutlining = True
ActiveSheet.Protect UserInterfaceOnly:=True

The first is just in case.
The second allows you to use outlining on a protected sheet. These setting will not change by itself so you shouldn't need to do it after further unprotect /protect operations on this sheet.

 

[gubertu]

Many thanks for your help.

I did what you proposed and It worked well, but when I close and reopen the book (saved in .xlsx), I cannot expand and collapse.

Probably with your code I could work with my protected file in .xlsx and expand and collapse, but I think I´m missing something...

Thanks!

 

[bobsan42]

Sorry - you are right. These settings need updating on opening.
Well, one way is use XLSM (or XLSB or simply XLS if possible) file type and change the settings for the sheet when the Wb is opened.
There is another way though: you can create an add-in file to be loaded in the background, and it will change the settings, when this Wb is opened.
Code for detecting opening of another Wb can be found here: https://www.jkp-ads.com/articles/FixLinks2UDF01.asp

 

[gubertu]

Sorry for answering so late.

Finnaly I have dediced to save the fie in .xlsm and insert in my protecting code "EnableOutlining = True ".

Thanks for your time!