Enable Autofilter while sheet is protected

[jjt1973]

I have a spreadsheet that needs to be protected to protect the many formulas I have going on. This spreadsheet will be distributed as a template to multiple people. I have an autofilter running in the range A8:A52 where rows uhide when "Value does not equal 0". I have the following macro running to auto update the filter if data changes which is working great:

Private Sub Worksheet_Calculate()

If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With


With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If

End Sub

The problem is, I have tried these macros to enable the filter to work when the sheet is protected but they aren't working and actually disrupt my data.. The two macros I have tried look like:

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Data")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="test1", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub


and

Private Sub Workbook_Open()
CCW.Protect Password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
CCW.EnableAutoFilter = True
End Sub
End Sub

CCW is the name for sheet one...

Also, I do have "use autofilters" checked in the protection menu. I am really hoping somebody can help me as I feel as if I have tried everthing.

Thanks,
jjt

 

[CWatts]

EDIT: Nevermind, I missed your second to last line which says you checked "Use Autofilter"....

I fiddled around a bit and the issue seems that the Use Autofilter will only allow the manipulation of the filter, not the adding or removing of it..