VBA Allow features in a protected worksheet 1

[MM1963]

I’m looking to protect a worksheet, but allow grouped columns to be collapsed or expanded. Also would like to allow the user to set filters and hide columns. I put the below code in the “ThisWorkbook” object. This protected the worksheet but not with a password. The VBA below allowed the grouped columns to expand and collapse. It also allowed columns to be hidden, but did not allow filtering. Not an expert with VBA so looking for help. Also, if I decide to password protect, where would I enter that in the VBA?
Thanks for your help.

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect UserInterfaceOnly:=True
.Protect AllowFiltering:=True
.Protect AllowFormattingColumns:=True
End With
End Sub

 

[strongm]

>but did not allow filtering

SWell,. it does - but only if you've already got a simple filter, which Microsoft often refer to as an autofilter (basically the filter applied when you click the funnel icon) in place; you can change it. And it allows you to apply a n Advanced filter. What you can't do is ADD a NEW simple filter. Or, as the MS documentations says:

Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter

 

[MM1963]

Thanks for the reply. I created filters in the first few columns. When I reopened the file, the filters were there but did not function. I clicked on the filter arrow and there is no drop down.

 

[combo]

'Protect' method has some optional arguments, password among others, you can use them in single line:
[pre]With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="YourPassword", UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingColumns:=True
End With[/pre]

combo

 

[MM1963]

Thank you very much. Works for me.