Olap Cubes - Automatic Select

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