X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

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

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.

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
Go to the top of the page

Posts in this topic

Custom Search

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