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. Anonymous
    2016-04-07T21:29:42+00:00

    Been using this code in many of my workbooks for a while so thank you. However, all of the sudden, it seems, when I now open workbooks, they are automatically being protected.

    So, for example, if I unprotect a sheet or sheets, save the workbook, and then re-open it, the sheets are protected. It never used to do this.

    I can still group/ungroup in protected sheets, which is what I want, however, if I want to email someone an unprotected workbook they have to manually go through and unprotect each sheet, or if I am constantly saving closing and opening as I build a workbook, I have to unprotect everything over and over again.

    Thoughts?

    (Deleting the code fixes this problem, but then obviously I can no longer group/ungroup when protected)

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-03-28T11:14:05+00:00

    Let's say your sheet is named MySheet, and it is protected with password "secret". Change the code for Workbook_Open in the ThisWorkbook module to

    Private Sub Workbook_Open()

        With Worksheets("MySheet")

            .EnableOutlining = True

            .Protect UserInterfaceOnly:=True, Password:="secret"

         End With

    End Sub

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-03-28T10:48:40+00:00

    Hi HansV MVP

    It happens to me the same thing, when opening the workbook I'm prompted for the password, could anyone help out with this issue? it is driving me insane!

    Thanks.

    Was this answer helpful?

    0 comments