时间戳

首页 > 解决方案 > 我可以使用用户窗体中列表框中的选择来过滤数据透视表的数据透视表吗？

excel - 我可以使用用户窗体中列表框中的选择来过滤数据透视表的数据透视表吗？

问题描述

我正在创建一个仪表板，我希望用户在用户表单的列表框中选择一个产品组。我无法选择列表框来过滤特定数据透视表的数据透视项。如果用户在列表框中进行一项或多项选择，我想了解如何过滤数据透视项。

我已经在这几个小时了，我似乎无法连接列表框和数据透视表。我尝试了不同的事件并尝试使用 cmdbutton 执行但无济于事。

Private Sub lbxProduct_Change()

    Dim PvtTbl As PivotTable
    Dim pvtItm As PivotItem
    Set PvtTbl = PvtPage.PivotTables("pvtYoYChart1")

'   I need the pivottable to change whenever a selection is made in the listbox
'   right now, nothing happens when i make a selection
    For Each pvtItm In PvtTbl.PivotFields("Product Group").PivotItems
        If pvtItm <> Me.lbxProduct.Value Then pvtItm.Visible = False
    Next pvtItm

End Sub

我希望用户单击列表框中的项目，并且我的数据透视表会立即更新。非常感谢你们提供的任何建议！！

标签： excelvba

解决方案

        Private Sub ShowItems()
        
        ActiveSheet.ChartObjects("Chart 1").Activate
        
        '== Part 1 - Place the selected elements in an array
        Dim myArray() As Variant
        Dim i As Integer, count As Integer
        Dim msg
        Dim pvtItm As PivotItem
        
        count = 1
        For i = 0 To Lbx_Defaut_TRS.ListCount - 1
            If Lbx_Defaut_TRS.Selected(i) = True Then
                ReDim Preserve myArray(count)
                myArray(count) = Lbx_Defaut_TRS.List(i)
                count = count + 1
            End If
        Next i
          
        
        '== Part 2 - Add or remove the PivotItems from the chart
        
        
        With ActiveChart.PivotLayout.PivotTable.PivotFields("DEFAUT")
            For Each pvtItm In .PivotItems
                If IsInArray(pvtItm.Caption, myArray) = False Then
                    pvtItm.Visible = False
                Else
                    pvtItm.Visible = True
                End If
            Next pvtItm
        End With
        
        End Sub
    
 'The IsInArray function used (to check if the elements of the pivot 
'chart are in the array of the selected list ) comes from another post by @BRAD as 'follows 


    '===========================================
    'VERIFICATION IF STRING IS IN ARRAY
    '===========================================
     Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
        Dim i
        For i = LBound(arr) To UBound(arr)
            If arr(i) = stringToBeFound Then
                IsInArray = True
                Exit Function
            End If
        Next i
        IsInArray = False
    
    End Function

推荐阅读