jeudi 18 août 2022

Using MultiSelect Listbox with Pivot Table

I am fairly new to Excel and am wondering if this is even possible. Right now I have a dynamic listbox that will show the values from a pivot table. I have the listbox set with multi select, and am wondering if there is a way to connect the multiselect check boxes with the list box, where if I unselect the checkbox, it will remove that selection from a pivot table filter which will in turn change the totals shown in the userform linked below. If there is a better route to go as well, that would be much appreciated. Thank you!

Total Cost Table


    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



Aucun commentaire:

Enregistrer un commentaire