Enable Group/Ungroup in protected sheet

Anonymous
2013-01-02T12:41:00+00:00

Dear solver,

 I have an excel sheet which is renamed as "Emp Summary" which is protected without password.

 In this protected sheet, I want to enable the Group/Ungroup option to show/hide row no. 34 to 42.

 How to do that? Will macro must be require? If yes, then please write the code for me. I would appreciate if this could be done withouth a macro, 

 even by other trick/option.

 Also, if I send the same file to another user, will he/she also requires to enable macro at his/her end?

 Please help.

 Many thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2013-01-02T12:56:30+00:00

You need VBA for this, and the end user will need to allow macros for this to work.

Press Alt+F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.

Copy the following code into the module that appears:

Private Sub Workbook_Open()

    With Worksheets("Emp Summary")

        .EnableOutlining = True

        .Protect UserInterfaceOnly:=True

    End With

End Sub

This code will be executed automatically each time the workbook is opened.

Was this answer helpful?

300+ people found this answer helpful.
0 comments

91 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2014-08-21T05:59:23+00:00

    I don't think it will work in a shared workbook. Sharing a workbook is a bad idea anyway - it greatly increases the probability that the workbook will become corrupted.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-08-20T21:13:34+00:00

    Hi HansV,

    Thanks for the information.  Unfortunately I was not clear.  The groupings already exist.  I would like users to be able to select the + or - sign to expand or collapse the existing group.  Is that possible in a protected shared Excel 2013 file?

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2014-08-20T18:19:12+00:00

    wsh.EnableOutlining = True only lets the user expand and collapse an already existing outline on a protected sheet. It is not possible to create or remove an outline (or group/ungroup) on a protected sheet.

    You'd have to provide macros that

    • Unprotect the sheet
    • Perform one or more actions such as creating or removing an outline
    • Protect the sheet again.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-08-20T15:53:29+00:00

    I have tried the above codes both for the worksheet and the entire workbook and I receive the error "You cannot use this command on a protected sheet.  To use this command, you must first unprotect the sheet (review tab, Changes group, Unprotect Sheet button).  You may be prompted for a password.".

    I need to have the worksheet protected to ensure that certain cells are not edited while sharing the file.  I would like users to be able to Clear All Filters as well as Group & Ungroup sections of data.

    Private Sub Workbook_Open()

         Dim wsh As Worksheet

         For Each wsh In Me.Worksheets

             wsh.EnableOutlining = True

             wsh.Protect UserInterfaceOnly:=True

         Next wsh

     End Sub

    Do I need to enter the name of the tab or workbook somewhere in the above code?  If so please let me know where.

    Was this answer helpful?

    0 comments