UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Slicer Items, Office 2013    
 
   
kfield7
post Jun 25 2019, 09:19 AM
Post#1



Posts: 1,031
Joined: 12-November 03
From: Iowa Lot



I am trying to list out my slicer selection in individual cells, to no avail.
Here's the code, with some 'debugging attempts noted.
As you can see, I've tried a couple different ways to reference the target cells.

CODE
Public Function GetSelectedSlicerItems(SlicerName As String) As String
'purpose: get slicer items in a list, and list out in spreadsheet cells underneath the cell containing the calling formula.
'e.g., if =GetSelectedSlicerItems("Slicer_Departments") is in "Sheet1" and cell H10,
'I expect to get a comma separated list of items in cell H10 followed by
'the list of individual items in cells H11 and down.
'I DO get a comma separated list in H10, but no subsequent listing.
'
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    Dim iRow As Long
    Dim iCol As Integer
    Dim WS As Excel.Worksheet
    Dim WSname As String
    Dim i As Long
    
    On Error Resume Next
    Application.Volatile
    
    Debug.Print Application.Caller.Address
    iRow = Application.Caller.Row
    iCol = Application.Caller.Column
    Debug.Print iRow, iCol
    
    Set WS = Application.Caller.Parent
    'WSname = Application.Caller.Parent.Name
    Debug.Print WS.Name   'OK, provides the correct worksheet name.
    For i = iRow + 1 To iRow + 20

'***problem here
        'Sheets(WSname).Cells(i, iCol) = Null 'fails to clear the range
        WS.Cells(i, iCol) = Null 'fails to clear the range
'***
    Next i
    
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                iRow = iRow + 1
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                Debug.Print iRow, iCol 'OK, provides the correct row and column

'***problem here
                'Sheets(WSname).Cells(iRow, iCol) = oSi.Name 'fails to list the slicer items in the designated cells
                WS.Cells(iRow, iCol) = oSi.Name 'fails to list the slicer items in the designated cells
'***

                Debug.Print oSi.Name 'OK, I get the correct item names.
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
Set WS = Nothing
End Function
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 06:51 PM