Forum Discussion

zerogenome's avatar
zerogenome
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 table is placed.

 

My idea is to bring a list from the pivot table field to Combobox. Normally, I define a name on the table range and then link with code like 

Dropdown_Currency.ListFillRange = "Currency_list"

 

My goal is to make this dynamic list from pivotable too but since it's not a table how can I link to a specific field, "Category Name". But I'm not sure which property/expression I should use in order to do this.

 

Here is some part of my code

Dim Pivot_Field_CategoryType_Filter As PivotField
Dim ComboBox_CategoryType As OLEObject


Set Pivot_Field_CategoryType_Filter = Sheets("ModeListing").PivotTables("Pivot_Category").PivotFields("Category Name")


ComboBox_CategoryType.ListFillRange = Pivot_Field_CategoryType_Filter.DataRange.ID

 

'Pivot_Field_CategoryType_Filter.DataRange is where I want to get my list from
____________________________________________________________________________________________

Thanks for all answer and suggestion. If there are other methods to do this I would like to hear as well. But I strongly prefer to link it from pivot table if possible.

developer
Macros and VBA
  • JKPieterse's avatar
    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

2 Replies

  • JKPieterse's avatar
    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's avatar
      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

       

Resources