Pivot table filter as a combo box source

[marcelo667]

Hi everybody!

I´m having a huge problem with the excel. Hope you guys can help me.

My problem is that I want to use the topics of a filter in the pivot table as the source of a Combo Box.

I´ll try to exemplify that:
I have a pivot table with the filter "Citys" and then I have the options to filter: New York, Paris, London and Madrid.
My goal is to make this citys as a source to a combo box dynamicaly updated, using the VBA.

Any sugestions??

 

[Jerry Sullivan]

Welcome to the Board,

Try...

Private Sub ComboBox1_DropButton_Click()
    Dim pviItem As PivotItem
    Dim pvfField As PivotField
    Dim strCurr As String
    Set pvfField = Sheets("Sheet1").PivotTables("PivotTable1") _
        .PivotFields("Citys")
    With Me.ComboBox1
        strCurr = .Value
        .Clear
        For Each pviItem In pvfField.PivotItems
             .AddItem pviItem
        Next
        .Value = strCurr
    End With
End Sub

 

[marcelo667]

It worked perfectly!
Thank you very much JS411!