Setting a combobox Row Source = Pivot Table Page fields items

[Enkay62]

Hi.
Here's my problem (with excel 2000).
I have a userform with a combobox.
In a sheet I've a Pivot table with a page field ("Names&quot.
I would like to put code in initializing event of my userform so as to get the combo to contain exactly the pivot table page field items.
I think it's something like
Myuserform.combobox1.rowsource=Pivottable.......items(???).
Any help will be much appreciated.
Thank you in advance.
Happy new year (in advance as well).
Bye.
Nick

 

[Bowers74]

Hey Nick,

Happy Holidays and Happy New Year!!!

Private Sub UserForm_Initialize()
Dim pvtTbl As PivotTable
Dim pvtFld As PivotField
Set pvtTbl = Worksheets("Sheet4").Range("A1").PivotTable
For Each pvtFld In pvtTbl.PageFields
    ComboBox2.AddItem pvtFld.Name
Next pvtFld
End Sub

I hope this helps!!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Enkay62]

Thank you Mike. ....Fast and helpful as always...
I'll try your code immediately.
Thank you very much for answering and helping me.
Happy holidays to you too.
Bye.
Nick

 

[xlbo]

Just a very slight addition - if this will run on initialise - you are better off clearing out the combo 1st:

Private Sub UserForm_Initialize()
Dim pvtTbl As PivotTable
Dim pvtFld As PivotField
Combobox2.clear
Set pvtTbl = Worksheets("Sheet4&quot.Range("A1&quot.PivotTable
For Each pvtFld In pvtTbl.PageFields
ComboBox2.AddItem pvtFld.Name
Next pvtFld
End Sub

just to make sure you don't have duplicates or any extraneous data

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

oh - and you'll be needing to run this ONCE for your pivot table as well - keeps old data out of the pivottable

With activesheet.PivotTables(1).PivotCache
.MissingItemsLimit = xlMissingItemsNone
.Refresh
End With

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244