Filter/Unfilter using a single command button

[LoriD]

I have a macro that filters data at the click of a command button. Is there a way for Excel to know when my list is filtered, so if I click the button again, it will un-filter my list?

Thanks in advance!

 

[rikrak]

On 2002-10-04 15:46, LoriD wrote:
I have a macro that filters data at the click of a command button. Is there a way for Excel to know when my list is filtered, so if I click the button again, it will un-filter my list?

Thanks in advance!

Hi Lori,

To check if autofilter is on:
If Worksheets("Sheet1").AutoFilterMode Then MsgBox "AutoFilterMode is on"

To remove autofilter
If Worksheets("Sheet1").AutoFilterMode then Worksheets("Sheet1").AutoFilterMode = False

 

[LoriD]

I should have said before - the macro I have runs an advanced filter.

Can clicking the same button make Excel see if the list is filtered, and if so, un-filter it?
And - if the list is NOT filtered, it should still run the original macro to filter the list.

Thanks.

 

[rikrak]

On 2002-10-04 16:34, LoriD wrote:
I should have said before - the macro I have runs an advanced filter.

Can clicking the same button make Excel see if the list is filtered, and if so, un-filter it?
And - if the list is NOT filtered, it should still run the original macro to filter the list.

Thanks.

You can asign only 1 macro to a button, so the check and the setup of the filter has to be done in the same macro:

<pre>
Sub Filter()
If Worksheets("Sheet1").FilterMode Then
'disable filter
Worksheets("Sheet1").ShowAllData
Else
'setup the filter here
End If
End Sub
</pre>

 

[LoriD]

That makes sense. Thank you.