Selected Slicer Value to Populate Cell

Hi,

Basically I would like to list the selected slicer values from "Slicer_Project_Type3" in cell R1 on worksheet "Whiteboard"

Here is my code (that is unfortunately not working)

I've tried putting this code into ThisWorkbook:

Code:

---

Public Function SlicerSelections(Slicer_Name As String)
 FblSlicerSelections = ""
 Dim i As Integer
 With ActiveWorkbook.SlicerCaches(Slicer_Name)
    For i = 1 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then
            SlicerSelections = SlicerSelections & " " & .SlicerItems(i).Value
        End If
    Next i
 End With
 End Function

---

My slicer formula name is "Slicer_Project_Type3"

So I put, on the "Whiteboard" tab in cell R1

Code:

---

=SlicerSelections(Slicer_Project_Type3)

---

This should return the text from the activated slicer value, but it does not.

Still can't figure out how to accomplish my mission here.

Any ideas would be great.

Thanks.

I dont think SlicerCaches exist in the WORKBOOK object. It would exist in a WORKSHEET.

Your code expects the name of a slicer as a String:

Code:

---

=SlicerSelections("Slicer_Project_Type3")

---

I tried adding in the quotes and putting the code in the worksheet instead of the workbook. No luck. Any other thoughts?

Thanks.

I almost have this working:

Code:

---

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
For i = 1 To SlicerCaches.Count
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then Worksheets("Whiteboard").Range("R1").Value = sItem.Name
Next sItem
Next i
End Sub

---

This gets me each slicer value over written in cell R1. What I'm trying to do is have each selected slicer value listed out in R1, R2, R3, R4.

I'm stuck!

Your original code works fine for me with a normal pivot table (Not a PowerPivot one - I'm not sure yet if that's an OLAP issue or PP specific). What happened when you tried it?

Edit: just remembered - you will need to add:

Code:

---

    Application.Volatile True

---

to the start of the code to get it to update when you change the slicer.

Code:

---

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Dim sItem As Excel.SlicerItem
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
   
    Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
    For i = 1 To SlicerCaches.Count
   
        lastrow = Worksheets("Whiteboard").Cells(Worksheets("Whiteboard").Rows.Count, "R").End(xlUp).Row
        Worksheets("Whiteboard").Range("R1").Resize(lastrow).ClearContents
        nextrow = 0
        For Each sItem In cache.SlicerItems
           
            If sItem.Selected Then
           
                nextrow = nextrow + 1
                Worksheets("Whiteboard").Cells(nextrow, "R").Value = sItem.Name
            End If
        Next sItem
    Next i
End Sub

---

It can be done more simply

Code:

---

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim sItem As Excel.SlicerItem
    Dim lastrow As Long
    Dim nextrow As Long
    Dim i As Long
   
    lastrow = Worksheets("Whiteboard").Cells(Worksheets("Whiteboard").Rows.Count, "R").End(xlUp).Row
    Worksheets("Whiteboard").Range("R1").Resize(lastrow).ClearContents
    nextrow = 0
    For Each sItem In Target.Slicers("Project_Type3").SlicerCache.VisibleSlicerItems

        nextrow = nextrow + 1
        Worksheets("Whiteboard").Cells(nextrow, "R").Value = sItem.Name
    Next sItem
End Sub

---