Solved: Auto Filter in a Protected Sheet

[IgnBan]

How can I use this code in a protected Sheet
Sub UpdateRedFilter()
Application.ScreenUpdating = False
Rows("2:2001").Select
Selection.EntireRow.Hidden = False

Range("A2:E2000").AutoFilter Field:=4, Criteria1:="r"
Range("A1").Select
Application.ScreenUpdating = True
End Sub
This code is attached to a macro button on the Sheet.
When I lock (protect) and run the macro, it pops me an error. I checked "Allow Auto filter" in the Protection wizard, but still gives me an error. Any suggestion?

[Bob Phillips]

That allows you to use an exitsing filter, not to create one.

You need to unprotect the sheet, add the filter, then protect it again.

[IgnBan]

Xld, again thanks for replaying to my post. I apologized didn’t explain the problem well. I created the filter than protected the Sheet, but what I didn’t explain is that I have 3 buttons;
one to filter the data (this data is coming from another Sheet, same workbook)
Sub RedTag_Filter()
Range("A2:E2000").AutoFilter Field:=4, Criteria1:="r"
End Sub

another to clear the filter (for other purposes)
Sub Clear_RedFilter()
Range("A2:E2000").AutoFilter
End Sub

, and one more to update the filter
Sub UpdateRedFilter()
Application.ScreenUpdating = False
Rows("2:2001").Select
Selection.EntireRow.Hidden = False

Range("A2:E2000").AutoFilter Field:=4, Criteria1:="r"
Range("A1").Select
Application.ScreenUpdating = True
End Sub
In case If data is enter in the source Sheet I want it to show in the filter, so my problem is after protecting the Sheet and when I click the Update filter the error occur. I hope this give a better picture of my problem.

What I’m trying to do is filter data with certain criteria, but I want to update the filter. If there is any other way to do it can somebody help

[AAM]

before you change the filter add
ActiveSheet.UnProtect after the change lines add:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells
You can clean up or adjust the last two lines, I just copied what I use to protect everything but unlocked cells while allowing autofilters and sorting

[IgnBan]

AAM-xld
Tank you guys for your input, AAM your code works beautiful smooth.


Moderator if none else has any other question or input the thread can be close.

Thanks again guys!:thumb

[lucas]

IgnBan,
you can mark your thread solved by using the thread tools at the top of the page.