How to enhance a multiple value filter check box control with an invert selection option in Microsoft Excel
The comments and emails I received on the recent post Excel Dashboard Controls – Multiple Value Filters reinforced my conclusion that the original idea of providing a feature to switch back to a previous selection is more confusing than helpful.
However, there is another option to really improve the functionality of multiple value filters. Leonid, one of my few but faithful readers was kind enough to share a very interesting idea: add an additional input feature to let the user invert the actual selection.
Leonid provided 2 different solutions. Number 1 is a simple inversion symbol with additional explanatory text. Clicking on the symbol inverts the actual selection of filters:
The VBA code triggered when clicking on the symbol is straight forward:
Dim rng_cell As Range
For Each rng_cell In Range("myActualFilter")
rng_cell.Value = Not rng_cell.Value
Range("myCheckBoxAll").Value = _
The code simply inverts all target values of the check boxes from TRUE to FALSE and vice versa.
Here is the workbook for free download:
If the additional row bothers you, you may want to look at Leonid’s alternative option: use a hyperlink on the symbol to provide a tooltip (”Invert Selection”):
There is an additional cell and some more code necessary (on the dashboard worksheet), but the implementation is still pretty straight forward. Here is the workbook:
Leonid, many thanks for sharing your ideas here and for your workbooks. Your contributions are highly appreciated.