Scroll Through Report Filter Items

In Excel 2010, slicers were added to Excel, and they make it easy to select one or more items in a pivot table’s Report Filter.

slicerselect01

If you have an older version of Excel, or if you don’t have room for slicers, you can use a Spin Button to quickly scroll through the items in a Report Filter.

scrollfilter01

How It Works

The Spin Button is from the ActiveX Controls on the Ribbon’s Developer tab.

  • If you click the Up arrow, it triggers the SpinUp event, and that runs a macro which selects the next item in the Report Filter.
  • If you click the Down arrow, it triggers the SpinDown event, and that runs a macro which selects the previous item in the Report Filter.
  • The macros only affect the first Report Filter in the first pivot table on the active worksheet.

scrollfilter02

Get the Sample File and Code

To see how the Spin Button works, and to get the code, you can download the sample file from my Contextures website. The zipped file is in xlsm format, and contains macros.

There are instructions on that page for building your own spinner, or adapt the code in the sample file to your own workbook.

_________________

This entry was posted in Programming. Bookmark the permalink.

Leave a Reply

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