Clearly and Simply

Tag: checkboxes

  • 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.

  • Excel Dashboard Controls – Multiple Value Filters

    How to create a multiple value filter control including a “select all / none” option with Microsoft Excel

    Multiple Value Filter Control

    If you ever built interactive Microsoft Excel dashboards, I am betting the ranch that there was at least one with some kind of interactive filtering features. For selecting one out of several categories you probably used a drop down list (i.e. a combo box). If you need a multi filter control (i.e. selecting several categories) a multi select list box or several check boxes are the way to go. We had that before: In one of the first articles on this blog I already showed different techniques of how to implement a multi-select control feature on Microsoft Excel dashboards (Approach with Caution).

    So far, so good. However, what I didn’t discuss in that article is one obvious requirement you may have for a multiple value filter control on a dashboard: an option to let the user easily select all or none categories with one single mouse click. Especially helpful if you are having a lot of categories.

    Today’s post tries to fill this gap: this article shows several options of how to implement a multiple value filter control based on check boxes, including an additional check box to easily select and deselect all categories. As always, the post provides all Excel workbooks for free download. 

    (more…)