Selected Slicer Value to Populate Cell

[nirvehex]

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:



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



=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.

[ranman256]

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

[Aflatoon]

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

=SlicerSelections("Slicer_Project_Type3")

[nirvehex]

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

Thanks.

[nirvehex]

I almost have this working:



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!

[Aflatoon]

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:

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

[Bob Phillips]

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

[Bob Phillips]

It can be done more simply


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