VBA to grey out pivot charts dependent on filter selected

Hi,

I have a problem that i'm hoping someone can help me with. I have been working on a dashboard where data is coming from several sources, in several formats. The user can use slicers to dynamically slice and dice the underlying data and the graphs/graphics on the dashboard update accordingly. Lovely jubbly.

I have attached a simplified file with 4 tables for each different set of source data. The table feeds a pivot and the pivot feeds the chart that appears on the dashboard. Simples.

Thanks to Debra Dalgleish at the Contextures blog, and some help from people on here, i had a great piece of code that allowed me to use one slicer to update the values on all 4 pivots. In this case the slicer is called "Store".

The Problem:

I have noticed a situation where sometimes a store does not have an entry on one of the tables. I got round this initially by getting all unique store numbers from all the data sources to create a "Master" filter.

However, if you set the filter to a particular store and one of the underlying pivots has no entry for that store, then the pivot filter will remain on "All". This means the dashboard graphs end up being misleading. As 2 or 3 may be showing the data for an individual store, whilst the other two will show the aggregated figures.

I was thinking that i could get the code to check if each pivot filter and see if it matches the "Master" filter. If it didn't then to some how grey out, or put a line through the corresponding graph to indicate that no data is available for that store. Or other such indicator....

That's just an idea though, has anyone got any suggestions how i could account for this and what code i could use to achieve my goal? as you can probably tell, i am definitely not a VBA expert!

I am not sure what the ettiquette is for posting a macro workbook, so i am hoping i can just put it up here. Apologies if this is not the case!

Thanks in advance,

J

EDIT: Just thought, that this is actually more complicated when multiple items are selected on the filter...I think i may go away and see if i can write a macro that checks each table for of data against the master list, and if it finds a store is missing, to create a dummy entry in the source data. Not ideal, but it may just about be within my ability! (maybe...)