Populate a Combo Box with Pivot Table Values

Question

I have a combo box with a list of names, that filters a pivot table.
Each name I select changes the values shown at the pivot table.

I need a code that populates a second combo box with those pivot tables values. When I choose a name on the first combo box, the options at the second one will be the ones filtered by that name on the pivot table.

I have done that before in a simpler manner with fixed tables, but now the values are going to change constantly, and that's why I need to use a pivot table for that.

enter image description here

Please update question to include your code(if applicable) and any attempts you've made. Generally speaking you can do this by querying the values in the pivot table after the filter is applied. There are vba objects specific to pivot tables and then simply load those values into a combo box. — Mike

Asger Asger · Accepted Answer · 2019-05-26T18:17:07

You can use the DataRange of your PivotField as source for your ComboBox.

You may either add each cell's value to the ComboBox items ...

Dim aCell As Range
For Each aCell In ActiveSheet.PivotTables(1).RowFields(1).DataRange.Cells
    ActiveSheet.ComboBox1.AddItem aCell.Value
Next aCell

... or the whole ComboBox.List in one by a simple assignment:

ActiveSheet.ComboBox1.List = ActiveSheet.PivotTables(1).RowFields(1).DataRange.Value

I guess you used your "TIMBRA..."-list as the only row field in your pivot table.
If not, you can address the DataRange by names instead, e. g.

ActiveWorkbook.Sheets("...").PivotTables("...").PivotFields("...").DataRange

Populate a Combo Box with Pivot Table Values

1 Answers