VBA - protect sheet and allow grouping/ungrouping (outline)

[frostybear]

Hi - I have a file that needs to be protected but still allows for collapsing/uncollapsing grouped columns & rows, editing on unlocked cells etc. I have 2 sets of code that almost work, but one does everything except the grouping and the other does everything except the password protection. I'm going crazy!


Option 1:
This does everything I need EXCEPT the password protection piece which I could not figure out how to correctly incorporate into this:

Private Sub Workbook_Open()
    Dim wsh As Variant
    For Each wsh In Worksheets(Array("sheet 1", "sheet 2"))
        wsh.EnableOutlining = True
        wsh.EnableAutoFilter = True
        wsh.Protect UserInterfaceOnly:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            DrawingObjects:=False, _
            Contents:=True
    Next wsh
End Sub

Option 2:
This one does everything I need EXCEPT the ability to collapse/uncollapse grouped columns/rows:

Sub ProtectSheets()
    Dim wsh As Variant
    For Each wsh In Worksheets(Array("sheet 1", "sheet 2"))
        wsh.Protect Password = "password"
        wsh.EnableOutlining = True
        wsh.EnableAutoFilter = True
        wsh.Protect UserInterfaceOnly:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            DrawingObjects:=False, _
            Contents:=True
    Next wsh
End Sub

Hopefully this is an easy fix to get everything I need - I am definitely not an expert on this Thank you!

[Aussiebear]

Welcome to VBAX frostybear. Try the following.

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
    ws.Protect Password:="Test", userinterfaceonly:=True
ws.EnableOutlining = True
NextEnd Sub

[frostybear]

Thanks so much! Is there a way I can add on the other elements too? Allowing filtering, format changes, inserting comments, etc?

[Aussiebear]

Yes. Just add the lines required as per your post #2

[frostybear]

Thank you so much! I got it working perfectly with the below, HOWEVER I need the workbook this is being used in to be shared, and when it's shared I receive an error upon opening and it does not work/nothing is protected. Is there any way around this to make this code work in a shared workbook?

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
    ws.Protect Password:="password", userinterfaceonly:=True, _
    AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            DrawingObjects:=False, _
            Contents:=True
    ws.EnableOutlining = True
    ws.EnableAutoFilter = True
Next
End Sub

EDITED TO ADD: My main goal here is to allow collapsing/uncollapsing grouped columns, filtering, and formatting to a protected, multi-sheet, shared workbook. It's fine if the file needs to be unshared to activate the protection via VBA, but really just need the mentioned abilities while it is shared and protected. Is this possible?

[Aussiebear]

You need to unprotect the shared workbook first. The code below is a simplified stand alone version.

Sub Unprotect_Sheet()
    ActiveWorkbook.UnprotectSharing "YourPassword”
End Sub

In your case you might like to try

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
    ws.Protect Password:="password", SharingPassword:= "SharedPassword", userinterfaceonly:=True, _
    AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            DrawingObjects:=False, _
            Contents:=True
    ws.EnableOutlining = True
    ws.EnableAutoFilter = True
Next End Sub

[frostybear]

Thank you! I received an error that it isn’t compatible with the version, platform, architecture etc. assuming this means I am out of luck based on the version of excel I have? Also should clarify I meant shared as in sharing via the review tab, not the online method