Grouping and ungrouping locked cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2009512%22%20slang%3D%22en-US%22%3EGrouping%20and%20ungrouping%20locked%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009512%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20have%20any%20thoughts%20on%20how%20to%20enable%20users%20to%20use%20group%20and%20ungroup%20in%20a%20sheet%20with%20locked%20cells.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2009512%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%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2009694%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20and%20ungrouping%20locked%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F652811%22%20target%3D%22_blank%22%3E%40kingmanmp4%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20created%20an%20outline%2C%20you%20can%20allow%20users%20to%20collapse%20and%20expand%20it%20by%20using%20code%20in%20the%20ThisWorkbook%20module%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20With%20Worksheets(%22Sheet1%22)%0A%20%20%20%20%20%20%20%20.Protect%20Password%3A%3D%22secret%22%2C%20UserInterfaceOnly%3A%3DTrue%0A%20%20%20%20%20%20%20%20.EnableOutlining%20%3D%20True%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EChange%20%3CEM%3ESheet1%3C%2FEM%3E%20to%20the%20name%20of%20your%20sheet%2C%20and%20%3CEM%3Esecret%3C%2FEM%3E%20to%20the%20password%20you%20want%20to%20use.%3C%2FP%3E%0A%3CP%3EYou'll%20have%20to%20save%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm)%20and%20instruct%20users%20to%20allow%20macros%20when%20they%20open%20the%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20users%20to%20be%20able%20to%20specify%20their%20own%20grouping%2C%20you'd%20have%20to%20provide%20macros%20to%20do%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Does anyone have any thoughts on how to enable users to use group and ungroup in a sheet with locked cells.

Thanks

1 Reply

@kingmanmp4

If you have created an outline, you can allow users to collapse and expand it by using code in the ThisWorkbook module:

 

Private Sub Workbook_Open()
    With Worksheets("Sheet1")
        .Protect Password:="secret", UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
End Sub

Change Sheet1 to the name of your sheet, and secret to the password you want to use.

You'll have to save the workbook as a macro-enabled workbook (.xlsm) and instruct users to allow macros when they open the workbook.

 

If you want users to be able to specify their own grouping, you'd have to provide macros to do that.