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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
>

I'm not firm :-)

"Jamen Lone" <(E-Mail Removed)> schrieb im Newsbeitrag
news:%(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

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

"Peter T" <peter_t@discussions> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>