Using MultiSelect Listbox with Pivot Table

user_19535243 0
Viewed 33

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
pivot-table checkbox vba excel listbox
1 Answers

If I understand you correctly, below is a sample sub which will filter the page field of the pivot table based on the checked checkboxes in the Userform.

Have all the Checkboxes caption to the pivot item name of the ThePivotFieldNameToBeFiltered. The caption of the checkbox is used to filter the pivot field. Or if each item of the pivot field to be filtered is the name of each checkbox then change the ctrl.caption into ctrl.name in the test sub.

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

Separate the process to populate the TotalCostList listbox in the userform_initialize into another sub, name the sub PopulateTotalCostList IF you don't want to repeat the userform_initialize process. But if it's OK with you to repeat the userform_initialize then change call PopulateTotalCostList into call UserForm_Initialize

In order the sub won't run each time the user check/uncheck the checkbox, add a button to call the sub. So the user must click the button after he's done doing the check/uncheck of the checkboxes to see the result.

user_77562510
Related