controlling a pivot table using a combo box

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.

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@sbcglobal.net> wrote in message
news:1128477498.830010.118010@g47g2000cwa.googlegroups.com...
> 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.
>

Thanks William. It worked perfect.

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

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


<cailotto@sbcglobal.net> wrote in message
news:1128566084.667375.190660@g43g2000cwa.googlegroups.com...
> Thanks William. It worked perfect.
>

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

Thanks,
Pasha

"William Benson" wrote:

> I learned it from Mike Alexander. If you like working with Pivot Tables:
>
> http://www.mrexcel.com/pivottablebook.shtml
>
>
> <cailotto@sbcglobal.net> wrote in message
> news:1128566084.667375.190660@g43g2000cwa.googlegroups.com...
> > Thanks William. It worked perfect.
> >
>
>
>