Pivot Tables: Page field in a Combobox in another worksheet

Welcome to the Board.

You could try a ComboBox from the Control ToolBox:


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

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

hsalortb said:

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

Click to expand...

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

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

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.

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

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

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

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?

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.