Filter Field selection.

H

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top