UtterAccess.com
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    
 
   
kfield7
post Jun 25 2019, 09:19 AM
Post#1



Posts: 978
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
 
DanielPineault
post Jun 26 2019, 06:54 AM
Post#2


UtterAccess VIP
Posts: 6,774
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)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://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
 
kfield7
post Jun 26 2019, 07:44 AM
Post#3



Posts: 978
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    24th August 2019 - 06:51 AM