detect the filter mode ?
 

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 ?
--

Jamen

detect the filter mode ?
 

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

"Jamen Lone" wrote in message
...
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 ?
--

Jamen

detect the filter mode ?
 

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" <peter_t@discussions schrieb im Newsbeitrag
...
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

"Jamen Lone" wrote in message
...
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 ?
--

Jamen

detect the filter mode ?
 

I'm not firm :-)

"Jamen Lone" schrieb im Newsbeitrag
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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

"Jamen Lone" wrote in message
...
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 ?
--

Jamen

detect the filter mode ?
 

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" wrote in message
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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

"Jamen Lone" wrote in message
...
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 ?
--

Jamen

detect the filter mode ?
 

thanks .. that's exactly the thing I'm looking for :-)
you're great

"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...
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" wrote in message
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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

"Jamen Lone" wrote in message
...
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 ?
--

Jamen