Enable Group/Ungroup in protected sheet

Anonymous

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.

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 MVP Volunteer Moderator

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.

0 comments

91 additional answers

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 No comments
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 No comments
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 No comments
HansV 462.7K Reputation points MVP Volunteer Moderator

2016-08-18T11:39:03+00:00

You could specify the password for the worksheet(s) in the Workbook_Open procedure, and protect the Visual Basic code with a (different) password. Only those who know the Visual Basic password can then view and edit the code.

To do so, select Tools > VBAProject Properties... in the Visual Basic Editor.

Activate the Protection tab.

Tick the check box 'Lock project for viewing'.

Enter the password that you want to use in both boxes.

*** Make sure that you remember this password! ***

Click OK.

Don't forget to save the workbook.

Next time you open the workbook, you'll only be able to view the VBA code in the workbook if you provide the password.
Was this answer helpful?

0 comments No comments