SOLVED
Home

Protect 'summary' sheet from editing but enable grouping - is it possible?

%3CLINGO-SUB%20id%3D%22lingo-sub-352460%22%20slang%3D%22en-US%22%3EProtect%20'summary'%20sheet%20from%20editing%20but%20enable%20grouping%20-%20is%20it%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352460%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20document%20with%20several%20editors%20who%20should%20be%20allowed%20to%20input%20data.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20those%20individual%20editor%20spaces%20come%20together%20in%20a%20'summary'%20page%20which%20i%20want%20to%20protect%20from%20being%20edited.%26nbsp%3B%20However%2C%20I%20want%20'viewers'%20to%20be%20able%20to%20expand%20the%20rows%20(groups)%20so%20they%20can%20SEE%20the%20data%2C%20just%20not%20edit%20it%20(unless%20doing%20so%20on%20the%20appropriate%20tab)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible......%20grouping%20available%20on%20a%20protected%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20should%20I%20protect%20some%20other%20way%2C%20lock%20particlar%20cells...%20any%20suggestions%20welcomed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-352460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353636%22%20slang%3D%22en-US%22%3ERe%3A%20Protect%20'summary'%20sheet%20from%20editing%20but%20enable%20grouping%20-%20is%20it%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353636%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20code%20is%20set%20to%20run%20each%20time%20the%20worksheet%20is%20%E2%80%9Cactivated%E2%80%9D.%20So%20when%20you%20click%20on%20a%20different%20sheet%20then%20click%20back%20on%20the%20summary%20it%20will%20trigger%20the%20macro%20to%20run%20and%20set%20enable%20outline%20to%20false%3CBR%20%2F%3E%3CBR%20%2F%3EOther%20options%20would%20be%20to%20assign%20the%20macro%20to%20a%20button%20that%20you%20click%20on%20the%20summary%20page%20or%20to%20put%20the%20code%20to%20trigger%20when%20the%20workbook%20opens%20(%20relies%20on%20people%20clicking%20the%20%E2%80%9Cenable%20macros%E2%80%9D%20warning%20when%20they%20open%20the%20file)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353198%22%20slang%3D%22en-US%22%3ERe%3A%20Protect%20'summary'%20sheet%20from%20editing%20but%20enable%20grouping%20-%20is%20it%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353198%22%20slang%3D%22en-US%22%3E%3CP%3EFirstly%2C%20thanks%20so%20much%20for%20the%20answer%20given.%26nbsp%3B%20it%20helped%20in%20part.%3C%2FP%3E%3CUL%3E%3CLI%3EI%20couldnt%20understand%20why%20it%20was%20working%20one%20minute%2C%20but%20not%20the%20next.%26nbsp%3B%20then%20found%20out%20I%20needed%20to%20save%20as%20an%20xlsm%20file%20instead%20of%20xslx%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20done%20this%2C%20I%20now%20find%20when%20I%20go%20back%20to%20the%20document%20after%20closing%2C%20the%20changes%20are%20not%20saved.%26nbsp%3B%20If%20I%20'view%20code'%20I%20see%20the%20changes%20are%20still%20there%2C%20but%20'enable%20outlining'%20has%20reverted%20back%20to%20false....%26nbsp%3B%20So%20now%2C%20I%20can%20no%20longer%20expand%20the%20rows%20because%20they%20are%20protected.%26nbsp%3B%20To%20do%20this%2C%20I%20have%20to%20'view%20code'%20and%20save%20every%20time%20I%20want%20to%20open%20the%20document%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20i%20doing%20wrong%20please!%3F!%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352468%22%20slang%3D%22en-US%22%3ERe%3A%20Protect%20'summary'%20sheet%20from%20editing%20but%20enable%20grouping%20-%20is%20it%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352468%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20far%20as%20I'm%20aware%20you%20need%20to%20use%20VBA%20to%20do%20this%20using%20Sheet.EnableOutlining%20%3D%20True%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight%20Click%20on%20your%20summary%20tab%20and%20select%20View%20Code%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20901px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83462i9900513CB06773F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20paste%20this%20code%20in%20the%20window%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Worksheet_Activate()%0A%0AWith%20ActiveSheet%0A%0A.Protect%20Password%3A%3D%22XYZ%22%2C%20UserInterfaceOnly%3A%3DTrue%0A.EnableOutlining%20%3D%20True%0A%0AEnd%20With%0A%0A%0AEnd%20Sub%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20code%20could%20be%20put%20in%20a%20workbook%20open%20event%20or%20wherever%20is%20suitable.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
JulieG1974
New Contributor

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.

3 Replies
Solution

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

 

image.png

 

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)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies