controlling a pivot table using a combo box

[cailotto]

I've been looking for some help on this and I haven't had any success.
I found a macro that controls multiple pivots, but not one that
controls a pivot table from a combo box. Does anyone know how to do
this?

Your help is appreciated.

 

[William Benson]

I just learned how to do this at the Excel User Conference in Fort Worth.

You need to add the combo box from the forms toolbar.

The items that will fill the combo need to be entered into a list somewhere
in Excel, and be sure to include the item 'All' at the top
This range becomes the input range for the combo box.

You need to right click on the combo and pick Format Object.

The properties you need to change are the Input Range and the Cell Link.
Input Range should be as described above. Cell link can be anywhere out of
the way.

Then you need a formula next to the cell link cell.
=INDEX([X],[Y]) where [X] is the input range and [Y] is the cell
link cell address.

The next part is also simple:

Record a macro of the type change you would want to make to the pivot
table, and see what it looks like in the module that is created. Just change
this so that it looks like :

Activesheet.PivotTables("blahblahblah"").PivotFields("BlahBlah").CurrentPage
= _
ActiveSheet.Range([Y]).Value 'where Y is the address of the
index formula, in quotes of course.

Tie the combo box to this macro by right clicking on it and choosing
"Assign Macro"

Hope this works for you.

 

[cailotto]

Thanks William. It worked perfect.

 

[William Benson]

I learned it from Mike Alexander. If you like working with Pivot Tables:

http://www.mrexcel.com/pivottablebook.shtml

 

[Guest]

What would the changes in this method for OLAP based Pivot Tables?

Thanks,
Pasha

 

[AarAay]

Hi,
I tried all that you have said and made the Macro..
My situation is.. I make the pivottable from a Data Model. That brings in data from two different data sources.
Here is the code I am getting

Sub DBACSM()
'
' DBACSM Macro
'

'
ActiveSheet.PivotTables("State").PivotFields( _
"[DataSet_State_List].[Area].[Area]").VisibleItemsList = Array( _
"[DataSet_State_List].[Area].&[Boston Area]")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[DataSet_Capitol_List].[Area].[Area]").VisibleItemsList = Array( _
"[DataSet_Capitol_List].[Area].&[Boston Area]")
Range("B1").Select
End Sub

Now I need to set the value from the INDEX. Unfortunately I am not good with VBA coding and i have no idea how to do this. Quick help will be greatly appreciated.

Thank you
RA.