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-08-18T05:42:31+00:00

    Thanks for all this help. This code seems to work when the workbook is protected WITHOUT password.

    Yet I am puzzled by something: it seems these macros do not allow me to save the file protected WITH A PASSWORD AND have the desired effect (use of filters and outlining) when reopening.

    I cannot possibly put the password in the Workbook_Open() macro explicitly! (Otherwise there is no point in putting the password...)

    Any thought on that?

    Was this answer helpful?

    0 comments
  2. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-08-15T20:29:36+00:00

    I'm afraid I can't offer further help without seeing the workbook.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-08-15T20:15:47+00:00

    Afer trying everything, I typed the entire macro from scratch and did not get any message. However, it does not seems to work. My shee is protected, I wrote the private sub and excel does not let me group and ungroup.

    Was this answer helpful?

    0 comments
  4. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-08-15T20:04:11+00:00

    If you copied and pasted the code from this thread into the Visual Basic Editor, and then edited it, you could try the following:

    • Delete the entire line With Worksheets(...)
    • Type it in again from scratch.

    Sometimes unwanted characters are copied; typing the line from scratch gets rid of those.

    Was this answer helpful?

    0 comments