Forum Discussion

kingmanmp4's avatar
kingmanmp4
Copper Contributor
Dec 22, 2020

Grouping and ungrouping locked cells

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

Thanks

  • 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.

    • hrastashki's avatar
      hrastashki
      Copper Contributor

      HansVogelaar  It might sound a silly question to you, but could you please advise where should I paste the macro code ?

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        hrastashki 

        Press Alt+F11 to activate the Visual Basic Editor.

        You should see a pane on the left hand side (the so-called Project Explorer) with something similar to this:

         

        Double-click on ThisWorkbook.

        Copy the code into the module window that appears.

        Switch back to Excel.

        Save the workbook as a macro-enabled workbook (*.xlsm).

        Make sure that you allow macros when you open the workbook.

Resources