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: Most helpful
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2017-07-24T20:09:26+00:00

    Right-click the sheet tab of Sheet1 and select 'View Code' from the context menu. This activates the worksheet module. Does it contain any code?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-07-24T09:25:17+00:00

    Hi Hans,

    I'm using a code based on the above, exactly this one:

    Private Sub Workbook_Open()

        With Worksheets("Sheet1")

            .Protect Password:="psw", userinterfaceonly:=True

            .EnableOutlining = True

            .EnableAutoFilter = True

            If .FilterMode Then

               .ShowAllData

            End If

        End With

    End Sub

    It works perfectly, but I have a problem when I copy the "Sheet1". It creates a "Sheet1 (2)" and it works correctly, but when I go back to "Sheet1" the following message appears:

    The message is back everytime I move to "Sheet1" from another sheet. It disappears definitively if I press "OK" and then I press the arrows on the keyboard and move to any other cell.

    Which could be the root-casue and the solution?

    I read all other answers in this thread, but seems could not find a similar problem.

    Thanks in advance!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-05-11T20:57:42+00:00

    That worked!!!  Thank you so much!  You are a genius - gold star for you!!

    Thank you also for the very quick response!

    Was this answer helpful?

    0 comments
  4. HansV 462.7K Reputation points MVP Volunteer Moderator
    2017-05-11T20:08:21+00:00

    That is not as it should be - whenever you protect a sheet, locked cells should not be editable.

    Does it make a difference if you use

            wsh.Protect UserInterfaceOnly:=True, _

                AllowFormattingCells:=True, _

                AllowFormattingColumns:=True, _

                DrawingObjects:=False**, _**

    Contents:=True

    It shouldn't make a difference, but who knows...

    Was this answer helpful?

    0 comments