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]

Welcome to the Board.

You could try a ComboBox from the Control ToolBox:

Private Sub ComboBox1_Change()
    If ComboBox1.ListCount = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Worksheets("Sheet1").PivotTables(1).PageFields(1).CurrentPage = ComboBox1.Value
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox1_DropButtonClick()
    Static Disabled As Boolean
    Dim PItem As PivotItem
    Application.ScreenUpdating = False
    If Disabled = False Then
        ComboBox1.Clear
        ComboBox1.AddItem "(All)"
        For Each PItem In Worksheets("Sheet1").PivotTables(1).PageFields(1).PivotItems
            ComboBox1.AddItem PItem.Name
        Next PItem
        Disabled = True
    Else
        Disabled = False
    End If
    Application.ScreenUpdating = True
End Sub

The ScreenUpdating staements are there because funny things were happening on screen without them.

 

[hsalortb]

Andrew,

Thanks a lot for your answer.
The COMBOBOX is working properly.

Please tell me how can I obtain consulting services from your company in this kind of issues.

Best regards,


HS

 

[Andrew Poulsom]

Please tell me how can I obtain consulting services from your company in this kind of issues.

I'm not a consultant. I just answer questions on this board as a hobby when I have some spare time.

 

[hsalortb]

Hi again,

Also related with COMBOBOX in a worksheet separated with the PIVOT TABLE:

It's possible to put 2 COMBOBOX to filter a date range ? For example, in the PAGE FIELDS-1 I have a list of dates but I want to put these dates in a range with a CALENDAR or COMBOBOX:

Since date [COMBOBOX1] to date [COMBOBOX2]

Thanks a lot again.


HS

 

[Andrew Poulsom]

You can only select one item from a page field, can't you? So I don't understand why you need from and to dates.

 

[hsalortb]

Andrew,

I'm preparing an EXCEL report.
The PIVOT TABLE is connected with a SQL Server database.

This report include date ranges as a parameters, and don't know how to select a range in the Pivot Table, using an additional worksheet (to put some information from the Pivot Table worksheet).

I tried using 2 combobox, but, how you say, is not possible to select more than one item in a PAGE FIELD.

Thanks for your help.


HS

 

[Andrew Poulsom]

Maybe you can use the Command Text property to introduce your parameters:

http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

 

[Marshalls]

I think this is also what im trying to achieve, basically i want to change a pivot tables field in one sheet from another. I have changed the code shown above and here it is:

Private Sub ComboBox2_Change()
If ComboBox2.ListCount = 0 Then Exit Sub
Application.ScreenUpdating = False
Worksheets("PivotTable").PivotTables("Downtime").PageFields("[All Workcentres]").CurrentPage = ComboBox2.Value
Application.ScreenUpdating = True
End Sub

Private Sub ComboBox2_DropButtonClick()
Static Disabled As Boolean
Dim PItem As PivotItem
Application.ScreenUpdating = False
If Disabled = False Then
ComboBox2.Clear
ComboBox2.AddItem "(All)"
For Each PItem In Worksheets("PivotTable").PivotTables("Downtime").PivotFields("[All Workcentres]").PivotItems
ComboBox2.AddItem PItem.Name
Next PItem
Disabled = True
Else
Disabled = False
End If
Application.ScreenUpdating = True
End Sub

However when i click the combobox all i get is (ALL) and when i click that i get this error:

Runtime error "1004"
Unable to set the currentpage property of the pivotfield class?

Any ideas?

 

[Andrew Poulsom]

Can you post a samll but representative sample of your data using Colo's utility below? Also please specify how your PivotTable is laid out.