Enabling Outlining (Groups) in a Protected Sheet

Hi guys,

I have a 2010 Excel sheet that I want to protect, however I want the user to be able to use the groups I have set up.

When protected, the + and - symbols are not able to be used.

Could someone talk me through the steps to write a macro for this??

I have no VBA experience, I only know where to locate it.

I don't want this to be a complicated process for the end user, I just want them to open the document and the grouping still works.

Thank you kindly.

Hi Daffodil11,

First attempt unsuccessful.

Error.png

Let me confirm a few things:

1. "yoursheetname" is this eg. "Sheet2" ?
> and if I rename the sheet tab down the bottom to say " Student Results", do I need to rename the code to "StudentResults" or "Student_Results" or simply "Student Results".

2. At what stage do I protect my worksheet? (Do I create the code whilst unprotected, then protect, then save as .xls, then re-open?)

3. For ease of getting to know how this code works, I have made the password for the sheet protection at "password". Is this confusing things?

4. In the sheet protection stage, do any of the check boxes concern the code we are writing? (i.e "Insert Rows" [] Checkbox)

5. FYI I have: checked the box in the Trust Center > Macro Settings > 'Enable all macros'.
checked the box in the Trust Center > Macro Settings > 'Trust access to VBA project object model'


Hope you can help me out.

Merry Christmas from Australia.

Dafodill11,

I just got it to work!

I needed double click on "This Worksheet" tab, and copy your code. I initially went Insert > New Module.

Thank you for your help.

Regards,

Angus

Hi Daffodil11,

With regards to the code you wrote for me above, (working great by the way), I wanted to take it one step further.

With the code above I have Enabled Outlining, and at present this code overwrites the standard "Protect Sheet" options (see image attached).

Would you please teach me how to write codes so that I can enable other features in the "Protect Sheet" checklist? (image attached)

Example: I also want to let users Add and Delete Rows.

Example: I also want to let users Format Cells

Hope you can help!

Looking forward to hearing from you.Protect Sheet Options.png

Thanks for your response Daffodil - and thank you for the short tutorial of making notes.

Here's where I'm at with my code:

---

Private Sub Workbook_Open()

With Worksheets("Sheet 2")
.Unprotect "password"
.EnableOutlining = True
.Protect "password", _
contents:=True, _
userInterfaceOnly:=True, _
DrawingObjects:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

End With

End Sub

---

Observations: 1. I was forced to change "contents" and "Userinterfaceonly" to TRUE... (FALSE basically unprotected the sheet altogether, and undid the previous code to enable outlining).

2. The insert row function works ONLY on rows that contain a complete row with NO formulas or Locked cells. (This is not ideal)
> If a row contains a locked cell (i.e formula), I cannot select that row to copy and past above.
> I want, if need be, to be able to select say 5 rows below with formulas in place, and insert them above. (Think of a register and as more lines are needed I add more as I go.. I just want the formulas to come with me.)
> This is all achievable if I give my users the password to unprotect the sheet, they insert more rows as necessary and the workbook will lock itself when the close... it just seems silly.


3. If I wanted this code to work on Sheets 1, 2 & 3 (all in same workbook), how do I write that into the text?
> I tried this: With Worksheets("Sheet 2", "Sheet 1", "Sheet 3") but it didn't work.


Thanks again in advanced for your time.

Regards.