Pivot Tables: Page field in a Combobox in another worksheet

[hsalortb]

Hi,

How can I put (or control) a PAGE FIELD of a Pivot Table in another worksheet in the same MS Excel workbook.

Thanks.

HS

 

[Andrew Poulsom]

Right sorry i didnt see that, anyways i ran the code and get a blank message box with the ok button.

What do you get with:

Sub Test() 
   Dim PItem As PivotItem 
   Dim Msg As String 
   For Each PItem In ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]").PivotItems 
      Msg = Msg & PItem.SourceName & vbCrLf 
   Next PItem 
   MsgBox Msg 
End Sub

 

[Marshalls]

exactly the same thing

 

[Marshalls]

This is a macro i made to get an idea of the code:

Sub changeworks()

ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]"). _
CurrentPageName = _
"[All Workcentres].[All Workcentres].[Landscape & Walling].[Marshalls L&W].[Southern].[RV - St Ives].[St Ives Flag and kerb dept.].[St Ives Press 2]"
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub


This is specific though to change from one machine to antoher as you can see there is muliple works ect ect.

Dont know if that help?

 

[Andrew Poulsom]

exactly the same thing

I think I'm stuck then. Last possibility:

Sub Test() 
   Dim PItem As PivotItem 
   Dim Msg As String 
   For Each PItem In ActiveSheet.PivotTables("Downtime").PivotFields("[All Workcentres]").PivotItems 
      Msg = Msg & PItem.Value & vbCrLf 
   Next PItem 
   MsgBox Msg 
End Sub

 

[Marshalls]

nope same again hmmm

 

[Andrew Poulsom]

nope same again hmmm

Hmmm indeed. Sorry, I don't think I will be able to help further.

 

[Marshalls]

Ok thanks anyway Andrew and do you know if there is a control i can use to replicate the one on the pivot table where you can drill down into specific regions,sites ect ect

 

[Andrew Poulsom]

Ok thanks anyway Andrew and do you know if there is a control i can use to replicate the one on the pivot table where you can drill down into specific regions,sites ect ect

There is a TreeView control under More Controls in the Control ToolBox. But don't ask me how to implement it:

http://btmtz.mvps.org/treeview/

 

[hsalortb]

Andrew,

I put my EXCEL file in a WEBSITE.
When I click the COMBOBOX, the following message appear:

"Unable to set the _Default property of the PivotItem class"

Any idea about what is the problem ?

Best regards,


HS

 

[Andrew Poulsom]

Sorry, no, but there are limitations on interactivity when publishing to the web. Have a look in Help.