Need to use auto refresh filter in protected sheet

[khrizthian1]

Hi guys, I joined just to ask this question, I have a sheet and with a table that is HUGE. I have added a filter that when the user chooses 0 "boxes" the entire row is eliminated. Now, this has a certain level of difficulty as the data is not manually entered onto the table, the data is entered based on ANOTHER CELL elsewhere in the sheet based on a simple =E14. So, next step, if done manually, it would be easy to refresh the table but I wanted to do it automatically so I have used the below code so that when the user adds a value and the 0 changes to something else, the entire row appears on the chart and the values are shown, but when the user enters 0 the row is eliminated. Below is the code for the auto refresh on the autofilter

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 above code works, but only if the sheet is UNPROTECTED. Once, I protect the sheet and try to change the values the whole filter just dies and the whole table is displayed and the filter is eliminated. I just want a way to use what I have with a protected sheet so people cannot modify pictures and formulas. Please let me know if someone can find a way to do this. I have EXCEL 2013. CHECKING THE ALLOW USER TO EDIT FILTERS ON THE PROTECTING SHEET OPTIONS DOES NOT WORK.

[Break_Point]

Hi K,

When protecting the sheet have you allowed users to use the filter, this may be the problem.

Hope this help.

[khrizthian1]

Yea bro. Look at the bottom of my post. I stated I did this.

CHECKING THE ALLOW USER TO EDIT FILTERS ON THE PROTECTING SHEET OPTIONS DOES NOT WORK.

[Break_Point]

The only thing I can suggest is locking the sheet programmatically with enabling the use of filters:

Please Login or Register  to view this content.

[khrizthian]

The only thing I can suggest is locking the sheet programmatically with enabling the use of filters:

Please Login or Register  to view this content.

I get Run-Time error "1004"
Method 'AutoFilterMode of object '_Worksheet' failed

I put them both in the same worksheet SOURCE like this

Please Login or Register  to view this content.