VBA code to re-protect sheets of the workbook upon close and save that will also allow auto-filters to work

[Musical2018]

I am new to MrExcel and new to VBA and hope you will help me. I used the following code to protect my workbook sheets before close and upon save and it works great but I can't figure out how to write it to allow filters to still work on all tabs of the workbook when protected. There are several people using it and I need to be sure it doesn't get left unprotected but they need to be able to filter.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
        Const ccbsox As String = "ccbsox"

        For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="ccbsox"
        Next ws
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
        Const ccbsox As String = "ccbsox"

        For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="ccbsox"
        Next ws
End Sub

 

[Jerry Sullivan]

Welcome to MrExcel,

You can add a parameter to allow filtering, like this...

For Each ws In ThisWorkbook.Worksheets
   ws.Protect Password:="ccbsox", AllowFiltering:=True
Next ws

Be aware that this only allows the user of the protected sheet to use existing autofilters, it does not let them to add/remove autofilters to/from the worksheet.

btw, if you are wanting to use the Constant ccbsox that you declared at the top of your code to modify your password in one place, then you would use it without quotation marks.

For Each ws In ThisWorkbook.Worksheets
   ws.Protect Password:=[B][COLOR="#0000CD"]ccbsox[/COLOR][/B], AllowFiltering:=True
Next ws

 

[Musical2018]

Thank you so much! That's perfect!