Forum Discussion
Protect 'summary' sheet from editing but enable grouping - is it possible?
I have a document with several editors who should be allowed to input data.
All those individual editor spaces come together in a 'summary' page which i want to protect from being edited. However, I want 'viewers' to be able to expand the rows (groups) so they can SEE the data, just not edit it (unless doing so on the appropriate tab)
Is this possible...... grouping available on a protected sheet.
Or should I protect some other way, lock particlar cells... any suggestions welcomed.
As far as I'm aware you need to use VBA to do this using Sheet.EnableOutlining = True
Right Click on your summary tab and select View Code
Then paste this code in the window
Private Sub Worksheet_Activate() With ActiveSheet .Protect Password:="XYZ", UserInterfaceOnly:=True .EnableOutlining = True End With End Sub
This code could be put in a workbook open event or wherever is suitable.
3 Replies
As far as I'm aware you need to use VBA to do this using Sheet.EnableOutlining = True
Right Click on your summary tab and select View Code
Then paste this code in the window
Private Sub Worksheet_Activate() With ActiveSheet .Protect Password:="XYZ", UserInterfaceOnly:=True .EnableOutlining = True End With End Sub
This code could be put in a workbook open event or wherever is suitable.
Firstly, thanks so much for the answer given. it helped in part.
- I couldnt understand why it was working one minute, but not the next. then found out I needed to save as an xlsm file instead of xslx
Having done this, I now find when I go back to the document after closing, the changes are not saved. If I 'view code' I see the changes are still there, but 'enable outlining' has reverted back to false.... So now, I can no longer expand the rows because they are protected. To do this, I have to 'view code' and save every time I want to open the document again.
What am i doing wrong please!?!??
- Hi
The code is set to run each time the worksheet is “activated”. So when you click on a different sheet then click back on the summary it will trigger the macro to run and set enable outline to false
Other options would be to assign the macro to a button that you click on the summary page or to put the code to trigger when the workbook opens ( relies on people clicking the “enable macros” warning when they open the file)
