Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Slicer Items

Posted by: kfield7 Jun 25 2019, 09:19 AM

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.

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
    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
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
            GetSelectedSlicerItems = "No items selected"
        End If
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
Set WS = Nothing
End Function

Posted by: DanielPineault Jun 26 2019, 06:54 AM

Any chance you could post a sample workbook with a concrete example or two of the initial data and what you are wanting as a final result.

You may also like to review

Posted by: kfield7 Jun 26 2019, 07:44 AM

Thanks, Daniel.

I did not know this (from your link):
"I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel.
Since a UDF cannot do this..."

Rather than going the contested indirect route discussed in the thread, I used a different approach to accomplish my objective.