VBA edit - need to allow filtering after protection

[DME]

Hi all,

I have some code that is un-protecting each worksheet in my workbook, enabling outlining (to allow users the ability to use the group/un-group functionality) and then protecting the file again.

The issue, however, is that my protected sheets don't allow for the filters to be used.

Any suggestions on how I can modify the code to allow for this functionality to still take place? I'm happy to provide more details but my code is provided below.

Thanks in advance for any thoughts!

Private Sub Workbook_Open()
   For Each wks In ThisWorkbook.Worksheets(Array( _
   "Sheet1", "Sheet2"))
        With wks
            .Unprotect "password"
            .EnableOutlining = True
            .Protect "password", contents:=True, userInterfaceOnly:=True
        End With
    Next
End Sub

 

[vletm]

DME
Have You tried to add next line in the end of Your 'protect ...' row
, AllowFiltering:=True

 

[DME]

Hi @vletm , I did. I added the following just below the ".EnableOutlining = True"

.AllowFiltering = True

 

[DME]

Oops, hit post too quick

That didn't work so I figured I was doing something incorrectly...

 

[vletm]

DME
Did You used "," ... or do I try to answer 12hrs later again?
.Protect Password:= "password", contents:=True, userInterfaceOnly:=True, AllowFiltering = True
... there were few typos

 

[vletm]

DME ... here is syntax for Protect

 

[DME]

@vletm, that worked! many thanks - below is my final code in case anyone is interested

Private Sub Workbook_Open()
   For Each wks In ThisWorkbook.Worksheets(Array( _
   "Sheet1", "Sheet2"))
        With wks
            .Unprotect "password"
            .EnableOutlining = True
            .Protect "password", contents:=True, userInterfaceOnly:=True, AllowFiltering:=True
        End With
    Next
End Sub

 

[vletm]

DME
as You know
password is great password