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 :confused: :confused:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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.
![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
?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! :eek: :eek:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
?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.
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.
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.
?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: