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-09-26T07:27:33+00:00

Hi HansV,

I'm trying to use a selection of code you've provided and put it together but it doesn't seem to be working for me.

What I'm trying to achieve is to have all tabs in a work book protected with the password "Wallpaper" when opened. Also to enable the "Use Auto filter" option when protecting the tab. I have 59 tabs in the file and want this to apply to all.

The code I've pulled together is below but doesn't work, please can you let me know where I'm going wrong?

Excel doesn't seem to like the 3rd line - the tabs are names differently within the actual file, I thought Excel would recognise "Sheet1", "Sheet59".

Private Sub Workbook_Open()

Dim wsh As Variant

For Each wsh In Worksheets(Array("sheet1", "sheet59"))

wsh.EnableOutlining = True

wsh.Protect UserInterfaceOnly:=True, Password:="Wallpaper"

AllowUse AutoFilter = True

Next wsh

End Sub

Many Thanks for any help you can offer
Was this answer helpful?

0 comments No comments
Anonymous

2016-09-21T13:50:41+00:00

The code as written applies protection to the worksheet but does not apply a password.

If you are prompted for a password, just click OK in the prompt dialog - this would be true if you were trying to unprotect the worksheet. Again, just click OK when prompted for a password.

--

Regards,

Tom Ogilvy
Was this answer helpful?

0 comments No comments
Anonymous

2016-09-21T13:33:04+00:00

Hi HansV MVP,

I'm afraid I applied the suggested solution without reading the consequent posts.

Although I did not enter a password to protect the worksheet, I am getting promted to enter a password to unprotect. How do I get out of that without having to create the whole thing again?

Thanks

Jer29:11
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