Linking multiple slicers to a chart title

[AndrewPaulCooper]

I have one table of information containing Business, Site, Room and associated usage and date data.
This generates a single pivot table (Starting in cell AA1 so out of sight of the end users)
Which produces a chart from that pivot showing the data
The chart and the table are on one tab so I can move the slicers over the chart and create a dashboard effect.
I have three linked slicers;
1st showing Business
2nd showing Sites
3rd showing Rooms

I have some VBA code to capture which slicers have been pressed and display that in the chart title ~ so far so good.

Assuming all filters are unset and someone presses {site 4} only (skipping the top slicer) then
The business slicer correctly shows which businesses have a presence on those sites (They are in a dark blue colour and ordered at the top of the pile)
The Room slicer correctly shows which sites have a room on those sites (Again they are in a dark blue colour and ordered at the top of the pile)
However (and finally we are at the point of the question) the title wrongly shows All BG's site 4 all room's i.e. the code has not detected the shades of colour it only detects a pressed (or multiple pressed) options (where the colours are blue or white, not dark blue and light blue).

I have not initially included the spread sheet as it has 373 lines of real data and it would take an age to make it into demo data.

How do I update the code to show the inferred [dark blue] selections?

My code is;

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Dim NumItems As Integer, counter As Integer
  NumItems = Me.PivotTables(1).Slicers("str_BusinessGroup").SlicerCache.SlicerItems.Count
  outputstring = vbNullString
  counter = 0
  For Each slcritm In Me.PivotTables(1).Slicers("str_BusinessGroup").SlicerCache.SlicerItems
  If slcritm.Selected Then
  counter = counter + 1
  outputstring = outputstring & ", " & slcritm.Name
  End If
  Next
  If counter = NumItems Then
  outputstring = "All BG's"
  Else
  outputstring = Mid(outputstring, 2, 999)
  End If
  Range("N1").Value = outputstring

  NumItems = Me.PivotTables(1).Slicers("str_Site").SlicerCache.SlicerItems.Count
  outputstring = vbNullString
  counter = 0
  For Each slcritm In Me.PivotTables(1).Slicers("str_Site").SlicerCache.SlicerItems
  If slcritm.Selected Then
  counter = counter + 1
  outputstring = outputstring & ", " & slcritm.Name
  End If
  Next
  If counter = NumItems Then
  outputstring = "All Sites's"
  Else
  outputstring = Mid(outputstring, 2, 999)
  End If
  Range("N2").Value = outputstring

  NumItems = Me.PivotTables(1).Slicers("str_RoomName").SlicerCache.SlicerItems.Count
  outputstring = vbNullString
  counter = 0
  For Each slcritm In Me.PivotTables(1).Slicers("str_RoomName").SlicerCache.SlicerItems
  If slcritm.Selected Then
  counter = counter + 1
  outputstring = outputstring & ", " & slcritm.Name
  End If
  Next
  If counter = NumItems Then
  outputstring = "All Room's"
  Else
  outputstring = Mid(outputstring, 2, 999)
  End If
  Range("N3").Value = outputstring
End Sub

The chart title links to cell N4 which has this code;

=N1&" "&N2&" "&N3

 

[NARAYANK991]

Hi ,

In the absence of your file , all of this is just thinking out loud.

Suppose you have 7 businesses ; when you select site 4 , it is possible that all 7 businesses still remain selectable , it is also possible that a lesser number of businesses , say 5 , now remain selectable.

What does NumItems show when you step through the code ? Assuming that only 5 businesses are selectable , does it show 7 ( which is the maximum number of businesses ) or does it show 5 ?

Let us assume it shows 5.

When you enter the

For Each slcritm In
Me.PivotTables(1).Slicers("str_BusinessGroup").SlicerCache.SlicerItems

loop , it is clear that all 5 selectable slicer items will be selected , since the user has not specifically selected any one.

So when the loop is exited , counter will be equal to NumItems ; nothing can be done about it.

The only way would be to put NumItems to the maximum number of available items , but even this would not guarantee that counter will not be equal to this , since the user has not selected any items specifically.

The fool-proof way would be to prevent selection of any lower level slicer until the higher level selection has been done first ; a hierarchy of slicers is akin to a a hierarchy of dependent dropdowns , and it should not be possible to select from a lower level dropdown unless one has first made a selection from a higher level dropdown.

Narayan

 

[AndrewPaulCooper]

Thanks for your reply Narayan, I'm back from an Easter break.

In the absence of your file , all of this is just thinking out loud.

OK Fair point, I will bite the bullet and make it non specific, give me a couple of days.

Suppose you have 7 businesses ; when you select site 4 , it is possible that all 7 businesses still remain selectable , it is also possible that a lesser number of businesses , say 5 , now remain selectable.

All seven are selectable BUT if you select a light blue business then the chart shows no data (because that business doers not have a site there). If you select a dark blue business then the chart shows the filter for site and business.

What does NumItems show when you step through the code ? Assuming that only 5 businesses are selectable , does it show 7 ( which is the maximum number of businesses ) or does it show 5 ?

It always shows the maximum :-( . This is the difference between dark blue/light blue (implied available selections) and dark blue/White (actually selected)

.........

The fool-proof way would be to prevent selection of any lower level slicer until the higher level selection has been done first ;

Unfortunately the users do not always know which business runs a part of a site. They use rooms without caring who pays for them!

 

[AndrewPaulCooper]

Creating a non-specific table was easier than I thought ~ especially when I dropped 3/4 the data

It is an XLSM file but the only code is the example above.

 

[NARAYANK991]

Hi Andrew ,

See if this works.

Narayan

 

[AndrewPaulCooper]

Excel Ninja is a deserved title, thank you so much, that is EXACTLY what I wanted, and I can even understand what you did. Brilliant!