Unable to use Group/Outline in a protected sheet?

**ynot2010** · 03-23-2010, 06:25 PM

Hi all,

I have a protected worksheet using Excel 2007. However, i have grouping that i want to remain active for the user.

The problem is with the sheet protected, the grouping feature does not work?

I did some investigation on the web and found this code (refer bottom of the thread) which i inserted into the sheet tab via "view code". I saved the file and reopened but it still does not work.

When the spreadsheet opens i do select "Enable Macros" and the spreadsheet has the setting which enables all macros to work.

Suggestions please? Any other solutions would be greatly appreciated.

Cheers.

Please Login or Register  to view this content.

**teylyn** · 03-23-2010, 06:30 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll add the code tags for you this time, but I expect you to use them from now on.

In which module did you put the code? It needs to go into the ThisWorkbook module.

Can you unprotect the sheet and then work with the groups?

See the attached file. Does that work when you open it?

**ynot2010** · 03-23-2010, 08:20 PM

Hi Teylyn,

Apologies for the coding. I am new to this forum and its rules.

I have put your code in the "ThisWorkbook module" but when i open it the "unprotect sheet" dialog box appears.

I have a password set therefore why i thought i needed to include the code relating to "protect password".

I have attached your initial file but now with a password assigned "aaa". It provides the same issue i am having in my example.

Suggestions please?

If i do not protect my sheet, the outlining works fine however i need my spreadsheet to be protected for use by various parties.

Thanks again Teylyn.

**teylyn** · 03-23-2010, 10:55 PM

Your code does not include the password. I added the password to the code, saved the file, closed it and re-opened it. No issues.

Works fine here. I can collapse and expand all existing groups.

The code in ThisWorkbook now is:

Please Login or Register  to view this content.

Are you trying to create new groupings, by any chance? That will not work. You can only expand and collapse existing groups.

**ynot2010** · 03-23-2010, 11:39 PM

Thanks Teylyn,

It appears to work now (when only applied to one sheet), however, what is the code for multiple sheets in the same workbook that are protected?

I am using the same password for all sheets as shown attached.

When i try and copy the same code and re-label the sheet number i get the attached error message?

Your assistance is very much appreiciated!

Cheers.

**teylyn** · 03-24-2010, 03:50 AM

Hi, you can not have more than one Workbook_Open event macro. But you can have several commands in there, like this:

Please Login or Register  to view this content.

etc.

**mjchansen** · 02-29-2012, 06:42 PM

Where does this code go in my Excel 2007 file? I don't know how to add it...thanks for anyone's help.

Marty

Private Sub Workbook_Open()
With Sheet1
.Protect Password:="aaa", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

**teylyn** · 03-03-2012, 02:27 AM

Marty,

for the record:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

But seeing you're new, take this:

Workbook Event code should be added to the workbook code module:

Copy the Excel VBA code that you want to use
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit | PasteWelcome to the Forum, unfortunately: