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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2014-11-04T17:02:49+00:00

    HansV,

    Thanks so much for your help!!!  I realized that I was using the code in a new module and not within "This Workbook".  

    All the best,

    Bigneff

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-06-05T16:52:02+00:00

    This is really helpful but can it be expanded to all worksheets?

    Yes, like this:

    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

    Hi, I copied this exactly, and only some of my sheets in the workbook allow users to group/ungroup, while other worksheets give the error message that the sheet needs to be unprotected first.  Do you know why it is not applying to all the worksheets in the workbook? 

    Thanks!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-06-05T19:15:12+00:00

    As I recall, before Excel 2003, you could apply the UserInterfaceOnly:=True to a worksheet without supplying the password.  After that, Excel required that the password be supplied.  (I could be off on which version this change took place).

    so I am assuming that the troublesome sheets are password protected and the sheets that have no problem are not password protected (they may be protected but don't have an assigned password).

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-06-26T16:14:03+00:00

    Same thing for me. Once I re-open the sheet it asks for the password and the grouping won't work until I supply it.

    Was this answer helpful?

    0 comments