PDA

View Full Version : Solved: Auto - Filter (check on/off Status)



asingh
06-23-2006, 01:34 PM
The function "Selection.Autofilter" can be used to add/remove an auto filter to a selected row. If a row has auto filters all ready on it, then using "Selection.Autofilter" removes these auto - filters. If a row does not have auto - filters then using "Selection.Autofilter" adds auto - filters to that row. Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

Thanks a lot for the help.

asingh

HaHoBe
06-23-2006, 02:19 PM
Hi,

make good use of AutoFilterMode:

Option Explicit

Sub asingh()
If ActiveSheet.AutoFilterMode Then
MsgBox "AutoFilter: on"
Else
MsgBox "AutoFilter: off"
End If
End Sub
Ciao,
Holger

XLGibbs
06-23-2006, 05:30 PM
Or you can also use

Selection.AutoFilter to turn it on and

ActiveSheet.ShowAllData to turn it off

asingh
06-23-2006, 07:40 PM
Hi,

The option "If Activesheet.Autofiltermode ", seems to work perfect. I am able to trap if the auto filters are on or off.

When I tried the other method as suggested by Ha ha Ho Be, "Activesheet.Showalldata", I get the following run - time error "ShowAllData method of worksheet class failed". I made sure the filters were on when I tried this.

My purpose is solved using the IF condition, but I was still wondering if the ShowAllData method could be made to work, it does seem good to use..

thanks


asingh

asingh
06-23-2006, 07:42 PM
Sorry about that...XLGibbs had suggested "ActiveSheet.ShowAllData" method....!

geekgirlau
06-23-2006, 08:28 PM
Generally speaking I find that the purpose of detecting whether the autofilter is on or not is because you want to apply a filter with only your criteria. I use something like the following:


If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If


Or ...

On Error Resume Next
ActiveSheet.ShowAllData

malik641
06-23-2006, 10:04 PM
Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

asingh
I'm still curious if what he originally asked for is even possible....

asingh
06-24-2006, 04:20 AM
I get an "ShowAllData method of worksheet class failed" error when trying to use the method "ActiveSheet.ShowAllData", even if the sheet has auto - filters set on it....?

asingh
06-24-2006, 12:38 PM
Okay...both the methods are now working....to get "ActiveSheet.ShowAllData" to work, Auto - filters have to be on, and a filter critereon has to be set. I was not doing that. The auto - filters were on but the critereon was not defined.

thanks to all......

regards,

ASingh

sajina
06-29-2007, 04:48 AM
So how do you establish if an auto filter criteria is in place ?

What i would like to do is to show all data if a filter is in place. I know i can achieve that if i switch off all filtering and recreate the filtering but I want to keep the existing auto filter. Also I don;t like doing it by allowing ShowAllData to error and ignoring the error - i just want to make sure that all rows are visible and there are no filtered out rows. Doing Activesheet.showalldata fails if a filter criteria is not selected.

Regards

geekgirlau
07-01-2007, 08:25 PM
Doing Activesheet.showalldata fails if a filter criteria is not selected.

... which is why you have the "On Error Resume Next"

If the data is filtered and criteria is selected, the criteria is removed so all rows are displayed.
If there is no autofilter on, all data is already displayed.
If the filter is on but no criteria is selected, all data is already displayed.So to restate, the simplest solution is



On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo ErrHandler