Expand/Collapse in Worksheet not protected by VBA

gubertu

Board Regular
Joined
May 24, 2015
Messages
129
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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:
Code:
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

Board Regular
Joined
May 24, 2015
Messages
129
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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
 
Last edited:

gubertu

Board Regular
Joined
May 24, 2015
Messages
129
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,176
Members
409,854
Latest member
rickcoba
Top