how to select one pivotItem from PivotTables-CodePudding

I am trying to set a single pivot Item from PivotTables using VBA.I have tried below code but its not working as expected.

Sub test()                          
    Dim PvtItm As PivotItem

    For Each PvtItm In ActiveSheet.PivotTables("PivotTable6").PivotFields("dataBlock").PivotItems
        If PvtItm.Name = "data 1" Then
            PvtItm.Visible = True
        Else
            PvtItm.Visible = False
        End If
    Next PvtItm
End Sub

This is throwing error when execute above code. please help me to correct.

CodePudding user response：

You can't hide all of the items (and your current code could try to do that), so it's best to first show the one you want, then hide all of the others:

Sub test()
    Dim PvtItm As PivotItem

    With ActiveSheet.PivotTables("PivotTable6").PivotFields("dataBlock")
        .PivotItems("data 1").Visible = True   'show this first
        For Each PvtItm In .PivotItems         '...then hide the rest
            If PvtItm.Name <> "data 1" Then
                If PvtItm.Visible Then PvtItm.Visible = False
            End If
        Next PvtItm
    End With
End Sub

CodePudding user response：

As @Tim Williams mentioned, you can't hide all the items in a pivot table. To add to his answer, you need to check if data 1 exists in the pivot field before hiding any items.

To do this, we can use a helper function that returns a Boolean on if the value exists.

' Helper function to see if a pivotfield contains
' a specified value.
Public Function HasPivotItem(field As PivotField, value As Variant) As Boolean
    On Error Resume Next
    hasPivotItem = Not IsNull(field.PivotItems(value))
End Function

Using this function, we can now check if the value exists and if not display a message letting the user know you can't hide all the items in the pivot table.

Below is a refactored version of Tim's code to account for when the pivot field is missing the data 1 value.

Sub test()
    Const ONLY_VISIBLE_ITEM As String = "data 1"
    Const TABLE_NAME As String = "PivotTable6"
    Const FIELD_NAME As String = "dataBlock"
    Const ERROR_MESSAGE As String = "Can't hide all the items on this axis"

    Dim datablock As PivotField
    Set datablock = ActiveSheet _
                    .PivotTables(TABLE_NAME) _
                    .PivotFields(FIELD_NAME)
    
    ' Pivot table needs at least one item in it. In this process
    ' hides all items but one, so if it doesn't exist then this
    ' process will throw an error.
    If Not HasPivotItem(datablock, ONLY_VISIBLE_ITEM) Then
        MsgBox ERROR_MESSAGE, vbCritical
        Exit Sub
    End If
    
    ' Safe to run as this value exists in the datablock field
    datablock.PivotItems(ONLY_VISIBLE_ITEM).Visible = True
    
    ' And safe to hide all other items!
    Dim item As PivotItem
    For Each item In datablock.PivotItems
        If item.Name <> ONLY_VISIBLE_ITEM And item.Visible Then
            item.Visible = False
        End If
    Next
End Sub