Display in a cell the items selected in a pivot field

[olympiac]

Display in a cell the items selected in a pivot field

Pivot field contains the folling values:
Code 1
Code 2
Code 3
Code 4

Value selected are "Code 1" and "Code 2"

Is there away to display in a cell the values selected?
Eg: Cell A1 will display: "code 1 - code 2"

 

[Jerry Sullivan]

You could try using a Worksheet_Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim strFlist As String
 
    With PivotTables("PivotTable1").PivotFields("Codes")
        For i = 1 To .PivotItems.Count
            If .PivotItems(i).Visible = True Then
                strFlist = strFlist & .PivotItems(i) & " - "
            End If
        Next i
        If Len(strFlist) > 1 Then
            strFlist = Left(strFlist, Len(strFlist) - 3)
        End If
    End With
 
    Application.EnableEvents = False
    Range("A1") = strFlist
    Application.EnableEvents = True
End Sub

 

[olympiac]

Does it work for you? I can see the Cell A1 highlighting but nothing is written in there.
Am I missing anything?

 

[Jerry Sullivan]

Does it work for you? I can see the Cell A1 highlighting but nothing is written in there.
Am I missing anything?

Yes, it works for me.
Does your field name in your code match the one in your Pivot Table?
(I used "Codes", but yours is probably different).

You might add a breakpoint and watch the values strFlist and i to help spot the problem.

 

[Jerry Sullivan]

Does it work for you? I can see the Cell A1 highlighting but nothing is written in there.
Am I missing anything?

In reviewing Olympiac's file, I learned the problem was that my proposed code worked for filters in the
the RowField section, but not for filters in the PageField section.

A post by DonkeyOte on another Board indicates that this was a problem prior to xl2007; but that one should be able
to iterate through PivotItems and check the .Visible status in xl2007.
http://www.excelforum.com/excel-pro...lected-items-in-a-pivot-table-page-field.html

My findings were that I could Set a PivotItem's Visible status,
but not read the status if the field is in the PageField section.

The workaround suggested by DonkeyOte for versions prior to xl2007 is to temporariliy switch the .Orientation.

Below is revised code with that fix, which appears to do the trick.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, lngOSave As Long
    Dim strFlist As String
    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Codes")
        lngOSave = .Orientation
        If lngOSave = xlPageField Then
            .Orientation = xlRowField
        End If
        For i = 1 To .PivotItems.Count
            If .PivotItems(i).Visible = True Then
                strFlist = strFlist & .PivotItems(i) & " - "
            End If
        Next i
        .Orientation = lngOSave
        If Len(strFlist) > 1 Then
           strFlist = Left(strFlist, Len(strFlist) - 3)
        End If
    End With 
    Sheets("Summary").Range("A1") = strFlist
Cleanup:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub