detect the filter mode ?

  • Thread starter Thread starter Jamen Lone
  • Start date Start date
J

Jamen Lone

Hi all,

I need a function to see if the autofilter is defined (so activ) and the
rows are selected

I tried it by this

Function FilterOn()
If ActiveSheet.FilterMode = True Then
... here should be "TRUE"
Else
..... here should be "FALSE"
End If
End Function


Can someone help me ?
 
Sub test()
MsgBox TickedFilters(ActiveSheet)
End Sub

Function TickedFilters(ws As Worksheet) As Long
Dim cntOn As Long
Dim af As AutoFilter, f As Filter

If Not ws.AutoFilter Is Nothing Then
If ws.FilterMode Then
Set af = ws.AutoFilter
For Each f In af.Filters
If f.On Then cntOn = cntOn + 1
Next
End If
End If
TickedFilters = cntOn

End Function

As written the function doesn't prove any rows are hidden, if you need that
look for hidden rows in ws.AutoFilter.Range

Regards,
Peter T
 
How should I use this function in a sheet ?

I tried =TickedFilters() in cell A1, but it doesn't work.
sorry, but I'm firm in vba.

Jamen

Peter T said:
Sub test()
MsgBox TickedFilters(ActiveSheet)
End Sub

Function TickedFilters(ws As Worksheet) As Long
Dim cntOn As Long
Dim af As AutoFilter, f As Filter

If Not ws.AutoFilter Is Nothing Then
If ws.FilterMode Then
Set af = ws.AutoFilter
For Each f In af.Filters
If f.On Then cntOn = cntOn + 1
Next
End If
End If
TickedFilters = cntOn

End Function

As written the function doesn't prove any rows are hidden, if you need
that look for hidden rows in ws.AutoFilter.Range

Regards,
Peter T
Click to expand...
 
I'm not firm :-)

Jamen Lone said:
How should I use this function in a sheet ?

I tried =TickedFilters() in cell A1, but it doesn't work.
sorry, but I'm firm in vba.

Jamen
Click to expand...
 
You mean you want to use it as a UDF, try this

Function TickedFilters(cell As Range) As Variant
Dim ws As Worksheet
Dim cntOn As Long
Dim af As AutoFilter, f As Filter

On Error GoTo errH
Set ws = cell.Parent
If Not ws.AutoFilter Is Nothing Then
If ws.FilterMode Then
Set af = ws.AutoFilter
For Each f In af.Filters
If f.On Then cntOn = cntOn + 1
Next
End If
End If
TickedFilters = cntOn
Exit Function
errH:
TickedFilters = Err.Description

End Function


in a cell
=TickedFilters(A1)+NOW()*0

the cell ref can be any cell on the sheet, probably better to make it the
same cell as the formula cell. the NOW stuff is to help it update if user
changes a filter setting.

Regards,
Peter T



Jamen Lone said:
How should I use this function in a sheet ?

I tried =TickedFilters() in cell A1, but it doesn't work.
sorry, but I'm firm in vba.

Jamen
Click to expand...
 
Back
Top