VBA to Clear Filters when Opening a Protected Worksheet

[cwmlpn]

I am a newby when it comes to working with VBA a Macros so please excuse my ignorance. I appreciate the help I have found on this forum.
64-bit operating system with Excel 2016 32-bit

I have a workbook with 3 worksheets. Sheet one (Action Plan) contains a table with filters turned on that will need to be protected. Multiple people will be using this worksheet with filters so I am needing a code that will automatically reset the filters on a protected worksheet upon opening. (If it help at all, users will only need to interact with Sheet One)

I have a code that works for clearing the filters when opening the workbook, but it only works when the worksheet is not protected. Please see the code below. Where do I add the code to unprotect and then protect the worksheet? Also, when using VBA to protect/unprotect, do I still protect the worksheet from the toolbar before closing?

Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In Worksheets

If wks.FilterMode = True Then

wks.ShowAllData

End If

Next wks

End Sub

Thanks in advance for your help!
cwmlpn

 

[rlv01]

This is the basic strategy

Private Sub Workbook_Open()

    Dim wks As Worksheet
    Dim MyPassword As String

    MyPassword = "12345"

    For Each wks In Worksheets

        If wks.FilterMode = True Then
            wks.Unprotect Password:=MyPassword
            wks.ShowAllData
            wks.Protect Password:=MyPassword
        End If
    Next wks

End Sub

Note that this assumes that the password for all sheets is the same. If it is not, then code to match individual passwords to the specific sheets must be added. Also, anyone who can open the vba project can see your passwords; so if you don't want that to happen you also need to password protect your vba project.

 

[cwmlpn]

Thanks for the reply. The code works to reset the filter in a protected sheet, however now the user is not able to use the filters at the top of the columns?

 

[rlv01]

Password protect can be configured in a lot of ways. Change the protect line to this:

    wks.Protect Password:=MyPassword, AllowFiltering:=True

 

[cwmlpn]

Works perfectly. You Rock! Thanks so much.
cwmlpn