Filter Field selection.

[Heera]

Hi All,

I have a protected sheet but filter is allowed in the sheet and I want
a code which will show all the data if the user has applied filter on
some field.

Regards
Heera Chavan

 

[Dave Peterson]

Depending on how the worksheet was protected, you may be able to cycle through
all the fields and reset the filter in your code.

If you protect in code with something like:
Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="topsecret", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

Then you could use:
Option Explicit
Sub testme()
Dim iCol As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
If .FilterMode Then
For iCol = 1 To .AutoFilter.Range.Columns.Count
.AutoFilter.Range.Columns(iCol).AutoFilter field:=iCol
Next iCol
End If
End With

End Sub

(This won't work if you use Tools|Protection|protect sheet and allow
autofilter.)

==========

But what may be the simplest is to just unprotect the sheet, do the work and
reprotect the sheet.

with worksheets("somesheetname")
If .FilterMode Then
.unprotect password:="topsecret"
.ShowAllData
.protect password:="topsecret"
end if
end with