[VBA] Filter macro not working in protected/shared sheet. Way around?

[Metaripley]

I have got an protected and shared sheet.
Which I want some macros to run a filter. And I cant unshare/unprotect/filter/protect/share because other users may lose data ar have to save a copy of the file. (which is bad!)

So is there a way around this problem (programmable in VBA) so I can filter in a protected file without unprotecting it?

Sub filter()'
' filtertest Macro
'


'
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= _"done"
End Sub

 

[asusetyo]

Unprotect - Run Filter - Protect ?

 

[Metaripley]

As I already said in my main post... I cant. It is a shared file.

 

[asusetyo]

Maybe, before you protect it, check allow to use AutoFilter on protect worksheet option

 

[Metaripley]

I can manual use a filter but even with the auto-filter option checked the macro wont work.

 

[Metaripley]

I found somewhat of a solution.

Instead of locking the sheet with the lock sheet button at the ribbon, I now lock it using a VBA code.

Sub Protect()

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


End Sub

Doing this will enable macro filtering within a protected sheet without the need of unprotecting it all the time!