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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Slicer Items, Office 2013    
post Jun 25 2019, 09:19 AM

Posts: 1,045
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
post Jun 26 2019, 06:54 AM

UtterAccess VIP
Posts: 7,163
Joined: 30-June 11

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 https://stackoverflow.com/questions/1250175...efined-function

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post Jun 26 2019, 07:44 AM

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

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.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    29th February 2020 - 01:53 AM