Consulting

Results 1 to 8 of 8

Thread: Selected Slicer Value to Populate Cell

  1. #1

    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:

    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.

  2. #2
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    I dont think SlicerCaches exist in the WORKBOOK object. It would exist in a WORKSHEET.

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Your code expects the name of a slicer as a String:
    =SlicerSelections("Slicer_Project_Type3")
    Be as you wish to seem

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

    Thanks.

  5. #5
    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!

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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.
    Last edited by Aflatoon; 05-14-2014 at 03:30 AM.
    Be as you wish to seem

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •