Enabling Outlining (Groups) in a Protected Sheet

[anguspgreenwood]

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.

[daffodil11]

This VB code will let you do it.

Alt+F11 opens VB

Go to Insert -> Module

In the blank page that opens up paste this

Private Sub Workbook_Open()
    
    With worksheets("yoursheetname")
        .Unprotect "password"
        .EnableOutlining = True
        .Protect "password", contents:=True, userInterfaceOnly:=True
    End With    

End Sub

Change yoursheetname and both references to password as needed to match your own. This will unprotect the sheet at startup, set Outlining to True, and then re-lock it.

Now close that window, save the workbook as an .xlsm file, close, and re-open.


This event only happens at startup and isn't saved in the file, so you'll need to save the workbook and re-open to see its effect.

[anguspgreenwood]

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.

[anguspgreenwood]

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

[daffodil11]

Awesome. I did my best to explain I know nothing about, like the blind leading the blind.

Glad you were able to eventually figure it out.

[anguspgreenwood]

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

[daffodil11]

Thanks for the private message, I'm just terrible at getting back to things.

I wasn't able to find the exact source I pulled this code from, but here's a simple solution:

Click the Record Macro button in the bottom left corner of your workbook, then protect your sheet, and turn on all options! Stop recording, then hit Alt+F11 and go check out the newest module of your workbook and view the code.

Here's what I got:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
End Sub

Each of the properties set after the protect are things you can dictate in your own macro.

Taking that information, we can inject it into what you already have.

Note how some lines end in " _"? This is a way to jump to the next line down so that the code doesn't continue off the screen.

In other words:

As long as _
I end each _
line with a " _" _
Visual Basic code will _
recognize it as _
a single continuous code.

If it helps you understand or organize better, you can do it after each property.
You can also begin a line with an apostrophe to create a comment, which tells VB to ignore the whole line. (It's a good way to leave yourself notes but don't try to do it in the middle of a line of code, even after a _.)

Private Sub Workbook_Open()
    
    'This VB Code goes off after opening and unprotects the workbook to enables outlining
    'It then reprotects the book but with enabled insertion/deleting of rows, and formatting of cell

    'daffodil is somewhat nifty

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

End Sub

And there's your code with every possible option as far as protection options, with Formatting, Delete Rows, & Insert Rows enabled.

[anguspgreenwood]

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.

[daffodil11]

I'd highly recommend reposting in the VB Forums. They could probably fix this in a jiffy.

I'm imagining 'For Each' somewhere near the top, which would cycle through all worksheets and apply the code to all.

I'm terribly sorry I can't be of much further assistance.

[Izandol]

#2 is limitation of worksheet protection.
#3 you may try:

Private Sub Workbook_Open()
   Dim ws                          As Worksheet
   For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
      With ws
         .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
   Next ws
End Sub