Excel Multiple Value Filters with Invert Selection

How to enhance a multiple value filter check box control with an invert selection option in Microsoft Excel

Invert SelectionThe 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:

Invert Selection V01

The VBA code triggered when clicking on the symbol is straight forward:

Sub InvertFilters()
Dim rng_cell As Range
    For Each rng_cell In Range(“myActualFilter”)
        rng_cell.Value = Not rng_cell.Value
    Next rng_cell
    Range(“myCheckBoxAll”).Value = _
             Application.WorksheetFunction.And(Range(“myActualFilter”).Value)
End Sub

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:

Download Multiple Value Filter Control with Invert Selection v01 (Microsoft Excel 2003, 91K)

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”):

Invert Selection V02

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:

Download Multiple Value Filter Control with Invert Selection v02 (Microsoft Excel 2003, 96K)

Leonid, many thanks for sharing your ideas here and for your workbooks. Your contributions are highly appreciated.

Add a Comment

Your email address will not be published. Required fields are marked *