FilterMode: Change of Mode (On/Off): How To Detect Event?

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
Preamble:
As with one of my previous posts, this has turned from a question into a tip, as I think I just found the answer while posting!

(I'm using Excel 2003 today at home; but have not yet tested it on 2002, which I have at work).



Scenario:
I have a Worksheet with Autofilter enabled. It is Protected but the user is permitted to use Autofilter dropdowns.

(Incidentally, I love the way that on a protected sheet, the whole header cell becomes the target for the mouse cursor, rather than the tiny dropdown arrow. The same is true for protected cells with dropdown validation lists.)

There is an Embedded ActiveX CheckBox Control on the Worksheet. Its function is to allow the user to reset / remove all filters (ie to show all records), using the ShowAllData Method.

I need to set to this Control's Value = True if FilterMode is False.

AutoFilterMode will always be True, as the user is not permitted to to turn off AutoFilter entirely.

But FilterMode will vary.


:confused:
Problem:
I need to detect / catch / trap the event of FilterMode changing:
(1) from FilterMode = False (Off) to Filtermode = True (On); and
(2) from FilterMode = True (On) to Filtermode = False (Off).

I was going to ask how do I do this?

Applying or removing filters does NOT appear to trigger Worksheet events, such as Worksheet_Change, Worksheet_SelectionChange, Worksheet_BeforeRightClick.


:)
?Solution:
I just tried Worksheet_Calculate.

This seems to be triggered following any of these events:
(1) when a new filter is selected,
(2) when the current filter is reselected,
(3) when the filter "(all)" is selected,
(4) when the filter "(all)" is the current filter and it is reselected.

I now plan to test further, but expect that I'll have to test for whether FilterMode is On (True) or Off (False). And then set the Control value accordingly. I plan to place this code in the Worksheet_Calculate Event Procedure.


:eek:
?Pitfalls
If anyone thinks I'm heading in the wrong direction with this one, maybe they could warn me (or others). Or if you have a better way. Equally if you can confirm for me/us that it's the way to go, that would be great.

Thanks.
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top