excel 搭配枢纽分析表使用多重选取清单方块

fjnneemd 于 2022-12-01 发布在其他

关注(0)|答案(1)|浏览(222)

我有一个动态列表框，它将显示数据透视表中的值。
我有多个选择列表框设置。
是否有一种方法可以将多选复选框与列表框连接起来，如果我取消选中该复选框，它将从数据透视表筛选器中删除该选择，从而更改下面用户表单中显示的总计？
总成本表

Private Sub UserForm_Initialize()

    Me.StartUpPosition = 0
    Me.Top = Application.Top + Application.Height - Me.Height * 1.08
    Me.Left = Application.Left + Application.Width - Me.Width * 1.12

    Description = Sheet8.Range("A1").Value
    Material = Sheet8.Range("A2").Value
    Labor = Sheet8.Range("A3").Value
    SubContractor = Sheet8.Range("A4").Value
    Equipment = Sheet8.Range("A5").Value
    Other = Sheet8.Range("A6").Value
    TotalCost = Sheet8.Range("A7").Value
    Overhead = Sheet8.Range("A9").Value
    Profit = Sheet8.Range("A10").Value
    Phoenix = Sheet8.Range("A11").Value
    Bond = Sheet8.Range("A12").Value
    TotalPrice = Sheet8.Range("A14").Value
    DescriptionTotal = Sheet8.Range("B1").Value
    MaterialTotal = Sheet8.Range("B2").Value
    MaterialTotal = Format(MaterialTotal, "$#,##0.00")
    LaborTotal = Sheet8.Range("B3").Value
    LaborTotal = Format(LaborTotal, "$#,##0.00")
    SubContractorTotal = Sheet8.Range("B4").Value
    SubContractorTotal = Format(SubContractorTotal, "$#,##0.00")
    EquipmentTotal = Sheet8.Range("B5").Value
    EquipmentTotal = Format(EquipmentTotal, "$#,##0.00")
    OtherTotal = Sheet8.Range("B6").Value
    OtherTotal = Format(OtherTotal, "$#,##0.00")
    TotalCostTotal = Sheet8.Range("B7").Value
    TotalCostTotal = Format(TotalCostTotal, "$#,##0.00")
    OverheadTotal = Sheet8.Range("B9").Value
    OverheadTotal = Format(OverheadTotal, "$#,##0.00")
    ProfitTotal = Sheet8.Range("B10").Value
    ProfitTotal = Format(ProfitTotal, "$#,##0.00")
    PhoenixTotal = Sheet8.Range("B11").Value
    PhoenixTotal = Format(PhoenixTotal, "$#,##0.00")
    BondTotal = Sheet8.Range("B12").Value
    BondTotal = Format(BondTotal, "$#,##0.00")
    TotalPriceTotal = Sheet8.Range("B14").Value
    TotalPriceTotal = Format(TotalPriceTotal, "$#,##0.00")
    
Dim List As New Collection
Dim Rng As Range
Dim lngIndex As Long

LastRow = Sheet8.Columns("A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set Rng = Sheet8.Range("A18:B" & LastRow - 1)

TotalCostList.ColumnCount = 2

With TotalCostList
    .ColumnCount = 2
    .List = Rng.Value
    .BorderStyle = fmBorderStyleSingle
End With

With Me.TotalCostList
    For lngIndex = 0 To .ListCount - 1
        .List(lngIndex, 1) = Format(.List(lngIndex, 1), "$#,##0.00")
        .TextAlign = 1 - frmTextAlignLeft
    Next lngIndex
End With

Dim i As Long

For i = 0 To TotalCostList.ListCount - 1
    TotalCostList.Selected(i) = True
Next i
    
End Sub

excel

来源：https://stackoverflow.com/questions/73410015/using-multiselect-listbox-with-pivot-table

1条答案

按热度按时间

qacovj5a1#

如果我没理解错的话，下面是一个示例子程序，它将根据用户窗体中选中的复选框来筛选数据透视表的页字段。
将所有的Checkboxes的标题设置为ThePivotFieldNameToBeFiltered的透视项名称。checkbox的标题用于筛选透视字段。或者，如果要筛选的透视字段的每个项都是每个checkbox的名称，则在测试子中将ctrl.caption更改为ctrl.name。

Sub test()
Dim pt As PivotTable
Dim ptFilterField As PivotField
Dim cek As Boolean: Dim ctrl

Application.ScreenUpdating = False

'set the pivot table as pt variable,
'change the name of the sheet and the pivot table as needed
Set pt = ActiveSheet.PivotTables("PivotTable1")

'set the pivot table filter field as ptFilterField variable,
'change the name of the pivot filter field as needed
Set ptFilterField = pt.PivotFields("ThePivotFieldNameToBeFiltered")

'loop to each ctrl in the Userform
'if the ctrl is a checkbox, then
'if checkbox is checked then have the pivot item name 
'(based on the checked ctrl.caption) of the ptFilterField visible, ELSE have 
'the pivot item name (based on the unchecked ctrl.caption) of ptFilterField not visible
'Also make cek variable in case the user uncheck all the checkboxes
'if it happen then it skip the filtering process and
'check all the checkboxes and have the ptFilterField to "(All)"
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
                ptFilterField.PivotItems(ctrl.Caption).Visible = True
                cek = True
            Else
                If cek = True Then _
                ptFilterField.PivotItems(ctrl.Caption).Visible = False
            End If
        End If
    Next ctrl
    
'below is the process if the user uncheck all the checkboxes
If cek = False Then
    ptFilterField.CurrentPage = "(All)"
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then ctrl.Value = True
    Next ctrl
End If

'call the sub to populate the TotalCostList listbox 
Call PopulateTotalCostList

Application.ScreenUpdating = True
End Sub

Private Sub CheckBox1_Click()
Call test
End Sub

Private Sub CheckBox2_Click()
Call test
End Sub

Private Sub CheckBox3_Click()
Call test
End Sub

如果您不想重复userform_initialize过程，请将用于填充userform_initialize中TotalCostList列表框的过程分隔到另一个子进程中，并将该子进程命名为PopulateTotalCostList。但是，如果您可以重复userform_initialize过程，请将call PopulateTotalCostList更改为call UserForm_Initialize
为了使sub不会在用户每次选中/取消选中复选框时都运行，请添加一个按钮来调用sub。因此，用户必须在完成选中/取消选中复选框后单击该按钮才能查看结果。

赞(0）回复(0）举报 2022-12-01

我来回答

excel 搭配枢纽分析表使用多重选取清单方块

1条答案

相关问题

热门标签

最新问答