Hi,

I would like to select a value in a pivot field automatically from
information on a list box. My pivot table is built on an Olap Cube.

I already have the following code for changing the pivot:

ActiveSheet.PivotTables("Pivottable1").PivotFields ("[MKT]").CurrentPageName
= _
"[MKT].[All].[NEW YORK]"

Now I want the value chosen in the listbox to be used as a variable

LTemp = CurList.List(CurList.ListIndex)

ActiveSheet.PivotTables("Pivottable1").PivotFields ("[MKT]").CurrentPageName
= _
"[MKT].[All].[LTemp]"

But this doesn't work. Has anyone an idea if this is possible. I know it is
with a "normal" pivot table but is it also possible with a pivot table based
on an olap cube?

Thx, for your answers

Use the & operator to incorporate the variable, e.g.:

ActiveSheet.PivotTables("Pivottable1").PivotFields ("[MKT]") _
.CurrentPageName = "[MKT].[All].[" & LTemp & "]"



Agamemnon wrote:
Hi,

I would like to select a value in a pivot field automatically from
information on a list box. My pivot table is built on an Olap Cube.

I already have the following code for changing the pivot:

ActiveSheet.PivotTables("Pivottable1").PivotFields ("[MKT]").CurrentPageName
= _
"[MKT].[All].[NEW YORK]"

Now I want the value chosen in the listbox to be used as a variable

LTemp = CurList.List(CurList.ListIndex)

ActiveSheet.PivotTables("Pivottable1").PivotFields ("[MKT]").CurrentPageName
= _
"[MKT].[All].[LTemp]"

But this doesn't work. Has anyone an idea if this is possible. I know it is
with a "normal" pivot table but is it also possible with a pivot table based
on an olap cube?

Thx, for your answers



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html