Forum Discussion

Copper Contributor

Nov 29, 2019

Solved

How to link combobox.listfillrange to pivot table field (VBA)

There are 2 worksheets related to this problem. First is a worksheet call "UI_Testing" which contains my problem. (Combobox name "ComboBox_CategoryType") Second is "ModeListing" where my pivot ta...

developer

Macros and VBA

JKPieterse

Nov 29, 2019

zerogenome I added this code to the worksheet "ModeListing":

Private Sub Worksheet_Activate()
    Dim PT As PivotTable
    Set PT = Me.PivotTables(1)
    With PT
        .RefreshTable
        .RowRange.Offset(1).Resize(.RowRange.Rows.Count - 2).Name = "CatList"
        Worksheets("UITesting").ComboBox1.ListFillRange = "=catlist"
    End With
End Sub

JKPieterse

Silver Contributor

Nov 29, 2019

zerogenome I added this code to the worksheet "ModeListing":

Private Sub Worksheet_Activate()
    Dim PT As PivotTable
    Set PT = Me.PivotTables(1)
    With PT
        .RefreshTable
        .RowRange.Offset(1).Resize(.RowRange.Rows.Count - 2).Name = "CatList"
        Worksheets("UITesting").ComboBox1.ListFillRange = "=catlist"
    End With
End Sub

zerogenome

Copper Contributor

Dec 04, 2019

JKPieterse Thanks again for your answer. I apply your idea to mine and it works great.

Private Sub ComboBox_CategoryType_DropButtonClick()

    Dim Pivot_Field_CategoryType_Filter As PivotField
    Dim Pivot_Table_Category As PivotTable
    
    Set Pivot_Field_CategoryType_Filter = Sheets("ModeListing").PivotTables("Pivot_Category").PivotFields("Category Name")
    Set Pivot_Table_Category = Sheets("ModeListing").PivotTables("Pivot_Category")
    
    With Pivot_Table_Category
    .RefreshTable
    End With
    
    Pivot_Field_CategoryType_Filter.DataRange.Name = "Category"
    
    ComboBox_CategoryType.ListFillRange = "=Category"
    

End Sub