Populating combo box from pivot table

[bjorgen]

I'm trying to populate a combo box based on a pivot table

I'm able to get the first one populated and added to the filter but there are 6 more filters that I'd like to be able to set hierarchically.

Assume that I have the first combobox (combobox1) populated and have just fed that value into the pivot table filter. I'd like to then have the code run through each filter based on hierarchy 2 and if there is no data for that filter based on the top filter then don't add it to combobox 2, otherwise do add it.

Basically it's a way of setting dependent combo boxes hierarchically.

Range B2 is the dropdown for the filter, so changing it for each pivotitem. Range C11 is where the cell would be blank should the pivottable contain no data.

It's breaking at the "selection.value = pvtItem" line with run time error 1004, application defined or object defined error.

Any help is greatly appreciated!

dim pvtitem = as excel.pivotitem
....
    With UserForm1.ComboBox2
    For Each pvtItem In ActiveSheet.PivotTables("pepperoni").PivotFields("HierarchyFilter2").PivotItems


            Range("b2").Select
            Selection.Value = pvtItem
                       
            If WorksheetFunction.CountA(Range("c11")) <> 0 Then .AddItem
    Next
    End With

 

[bjorgen]

I was able to get this working, the problem being a bad data value of "" instead of (blanks) in the dropdown.