Block Selection of All in Report Filter

With a pivot table’s Report Filters, you can select one or more items, and see the summarized results for those items only. For example, you might want to see the sales in one region, or on a specific date.

You can also click on “(All)”, at the top of the items list, to see the overall results.

vbablockall03

Block Selection of “(All)”

In some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter.

In this example, the worksheet shown below has GetPivotData formulas that refer to the Report Filter selection in cell B1. If (All) is selected in cell B1, the formulas show a message, instead of the dollar amounts.

vbablockall02

Use Programming to Block “(All)”

Unfortunately, you can’t remove the (All) option from the report filter’s drop-down list, but you can use Excel VBA to block users from selecting it.

The code shown below will undo the report filter change, if (All) is selected or if someone checks multiple items in the filter. They’ll also see a message that explains what to do — "Please select a single date."

vbablockall04

This code is stored on the pivot table’s worksheet module, and can be activated by selecting from the filter drop down list.

If someone selects “(All)” from the OrderDate report filter’s drop-down list, they’ll see the warning message.

vbablockall

Download the Sample File

To download the sample file, which contains the pivot table and VBA code, please visit my Contextures website: Pivot Table Report Filters VBA.  The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.

_________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in pivot table programming. Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>