Filter in table not working after protecting sheet using macro

[1q2w3]

Hi,

I have a set of macro i used to hide rows in a table based on a selected drop box and is working fine after i added the protect sheet macro.

For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Dim A, Criteria As Range
Set A = Range("A1")
Set Criteria = Range("B1")
Select Case A
Case Is = "Show All":
Rows("5:100").EntireRow.Hidden = False
Case is = "Show B"
Rows("50:100).EntireRow.Hidden = True
End Select
ActiveSheet.protect Password:="password"
End Sub

However, within the table, when i attempt to use the filter function, it stopped working. Nothing happens when I clicked on it. But when i removed the ActiveSheet.protect password, then it works fine again.

How can I make the filter work even under protected condition?

Thanks

 

[nardagus]

Hi,

You have to add permissions to use autofilter after you enable protection:

This will allow you to use autofilter:
AllowFiltering:=True

(note I didn't use password in my piece of code)

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True

 

[1q2w3]

@nardagus Great thanks for that code! All working fine now! Cheers

 

[nardagus]

@nardagus Great thanks for that code! All working fine now! Cheers

Glad to help.