none
Use Slicers's Selection in Formula or in VBA RRS feed

  • Question

  • I have a pretty complex Data Model (built into PowerPivot), where I have some slicers in a Dashboard. All nice and dandy, however, I want those slicers to be applied to another Table which is Not (and can not be) part of the DataModel.

    Therefore, I am using an Excel UDF to capture the slicers’ selections. The part that I’m struggling with begins here. I need to use the selections that I capture from the slicers and use VBA to filter the UnLinked Table, OR use a SUMPRODUCT/SUMIFS formula to calculate the results I want from the UnLinkedTable. For example, if in the “PRODUCTS” Slicer I select “A”, “B”, “C” and “D”, my UDF formula returns: “A, B, C, D”. If everything is selected in the slicer, the formula returns “All Items”.

    Now, how do I filter in VBA the UnLinkedTable[PRODUCT] column using that “A, B, C, D” result, or how do I write a SUMPRODUCT or SUMIFS formula in Excel? Keep in mind that I have multiple slicers and multiple columns in the UnLinked table. I highly appreciate your help!

    Thursday, August 25, 2016 9:47 PM

Answers

  • I manage to solve my problem with a bit of VBA.

    If anyone is interested in the solution, here it is:

    [SelectionsInSlicerBrand] -> this is the named range for the cell where my UDF brings the slicer selection
    BrandFilters = Split([SelectionsInSlicerBrand], ",")
    If [SelectionsInSlicerBrand] <> "All Items" Then
     ActiveSheet.ListObjects("TableFilterVBA").Range.AutoFilter Field:=1, Criteria1:=(BrandFilters), Operator:=xlFilterValues
     Else
     ActiveSheet.ListObjects("TableFilterVBA").Range.AutoFilter Field:=1
    End If
    

    However, I now have a different issue. I have tested my Slicer UDF formula on a normal PivotTable, and it worked just fine. However, when I create a PowerPivot PivotTable, the UDF doesn’t work anymore.

    Based on what I read on the following link, it might have to do with the fact that the PowerPivot PivotTable is OLAP based, so I need to use SlicerCacheLevel, not SlicerCache, like my UDF uses: http://stackoverflow.com/questions/28718295/powerpivot-slicer-selection-based-on-cell-value-using-vba

    I will post here the full code for my UDF which again, brings the selection of a slicer of a normal pivot table just fine. If you can please change it to work for a PowerPivot as well, that’d be amazing. The UDF is found here: 'http://www.jkp-ads.com/Articles/slicers05.asp

    Public Function GetSelectedSlicerItems(SlicerName As String) As String
        Dim oSc As SlicerCache
        Dim oSi As SlicerItem
        Dim lCt As Long
        On Error Resume Next
        Application.Volatile
        Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
        If Not oSc Is Nothing Then
            For Each oSi In oSc.SlicerItems
                If oSi.Selected Then
                    GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                    lCt = lCt + 1
                ElseIf oSi.HasData = False Then
                    lCt = lCt + 1
                End If
            Next
            If Len(GetSelectedSlicerItems) > 0 Then
                If lCt = oSc.SlicerItems.Count Then
                    GetSelectedSlicerItems = "All"
                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
    End Function
    


    Friday, August 26, 2016 5:25 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, August 26, 2016 8:34 AM
  • I manage to solve my problem with a bit of VBA.

    If anyone is interested in the solution, here it is:

    [SelectionsInSlicerBrand] -> this is the named range for the cell where my UDF brings the slicer selection
    BrandFilters = Split([SelectionsInSlicerBrand], ",")
    If [SelectionsInSlicerBrand] <> "All Items" Then
     ActiveSheet.ListObjects("TableFilterVBA").Range.AutoFilter Field:=1, Criteria1:=(BrandFilters), Operator:=xlFilterValues
     Else
     ActiveSheet.ListObjects("TableFilterVBA").Range.AutoFilter Field:=1
    End If
    

    However, I now have a different issue. I have tested my Slicer UDF formula on a normal PivotTable, and it worked just fine. However, when I create a PowerPivot PivotTable, the UDF doesn’t work anymore.

    Based on what I read on the following link, it might have to do with the fact that the PowerPivot PivotTable is OLAP based, so I need to use SlicerCacheLevel, not SlicerCache, like my UDF uses: http://stackoverflow.com/questions/28718295/powerpivot-slicer-selection-based-on-cell-value-using-vba

    I will post here the full code for my UDF which again, brings the selection of a slicer of a normal pivot table just fine. If you can please change it to work for a PowerPivot as well, that’d be amazing. The UDF is found here: 'http://www.jkp-ads.com/Articles/slicers05.asp

    Public Function GetSelectedSlicerItems(SlicerName As String) As String
        Dim oSc As SlicerCache
        Dim oSi As SlicerItem
        Dim lCt As Long
        On Error Resume Next
        Application.Volatile
        Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
        If Not oSc Is Nothing Then
            For Each oSi In oSc.SlicerItems
                If oSi.Selected Then
                    GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                    lCt = lCt + 1
                ElseIf oSi.HasData = False Then
                    lCt = lCt + 1
                End If
            Next
            If Len(GetSelectedSlicerItems) > 0 Then
                If lCt = oSc.SlicerItems.Count Then
                    GetSelectedSlicerItems = "All"
                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
    End Function
    


    Friday, August 26, 2016 5:25 PM
  • >>>If you can please change it to work for a PowerPivot as well, that’d be amazing.

    According to your description, I suggest that you could use "Record Macro" to get VBA code.

    On the Developer tab, in the Code group, click Record Macro.

    Thanks for your understanding.
    Monday, August 29, 2016 8:39 AM
  • Hi David,

    I've managed to amend the code and now it works! 

    Friday, September 2, 2016 4:05 PM