Dashboard - Selecting Pivot Items for Multiple Charts

[hattrick_123]

Hi there

First time poster here. Apprciate any help/pointers.

I have come accross the below code here,that basically selects all the pivot items to be displayed in the pivot chart (including the blank).

What I am looking for is a way to only select one particular pivot item for all charts? Any ideas?

 ' this works but it shows blanks(need excel 07 to avoid this as you can create a pivot table first and from there a pivot chart, but it can still be avoided using code) as I will be selecting all the blanks for updating this
 ' taken from here [URL]http://excel.bigresource.com/Track/excel-vAvbel9M/[/URL]
Sub ShowAllItems()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim objPT As PivotTable
Dim objPTField As PivotField
Dim objPTItem As PivotItem
 
For Each ws In Worksheets
ws.Visible = xlSheetVisible
    For Each objPT In ws.PivotTables
 MsgBox "Pivot table name: " & objPT.Name & vbCrLf & "Sheet name: " & ws.Name
    With objPT
'
' Run through all the row fields
For Each objPTField In .RowFields
'
' Get the hidden items and make them visible
For Each objPTItem In objPTField.HiddenItems
objPTItem.Visible = True
Next 'objPTItem
Next 'objPTField
'
' Run through all the column fields
For Each objPTField In .ColumnFields
'
' Get the hidden items and make them visible
For Each objPTItem In objPTField.HiddenItems
objPTItem.Visible = True
Next 'objPTItem
Next 'objPTField
End With
Application.ScreenUpdating = True
 
    Next objPT
Next ws
End Sub

The above code is very generic and can apply to any set of data.

The way I am looking at doing it is using an if statement and linking this to a user form, which ultimately selects the desired pivot item.

Example below of what I am trying to do(excuse the syntax I am relativly new to this code )

Sub test_select()
    If b2 = 1 Then ' if 1 is selected then George is displayed in the Chart(s)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("George").Visible = True
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("Peter").Visible = False
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("blank").Visible = False
  ElseIf b2 = 2 Then ' if 2 is selected then Peter is displayed in the Chart(s)
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("George").Visible = True
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("Peter").Visible = True
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SGSN").PivotItems("blank").Visible = False
End If
End Sub