Thread: Drop Down Box linked to Pivot

Hi

I have a Drop Down / Combo box and when a value is selected I want it to change/update multiple pivot tables.

I am trying to get it to change for one pivot table first and I have the following code:

Sub DropDown1_Change()
ActiveSheet.PivotTables("PivotTable1").PivotFields("SMS").CurrentPage = [NOT SURE WHAT GOES HERE]
End Sub

I have read somewhere that I can't link directly to the values in a combo box, but I can use the cell link? If so, how do I do that?

Thanks in advance....

Thanks for the prompt response!

But i am not sure what event will trigger the code?

Thanks. I got it to work but it isn't quite what I am after?

I would like something simpler like this example with images:

http://www.mcgimpsey.com/excel/lookuppics.html

Sorry - am using office 2007 and im still getting used to it.

Try this?

Thanks so much carim!

It worked perfectly at home in excel 2000...but it has an error in excel 2007 :S

It stops at this line

myField.CurrentPage = myFieldItem

and says

"Unable to get the CurrentPage property of the PivotTable"

Any ideas?

Perhaps this error message helps:

"object variable or with block variable not set"

ok ive worked it out and i can get it to work but for some reason as soon as it gets to this line

myField.CurrentPage = myFieldItem

executes and then goes back to the start

Private Sub Worksheet_Change(ByVal Target As Range)

and totally skips the loop..

so its ends up in an infinite cycle and i dont know why....

oh i know why!

because when its changing the first pivot table its causing a "worksheet change" which triggers the function to execute all over again...

any idea on what i should change the trigger to?