Passing Criteria to a Pivot Table from combo boxes

[Steve Muir]

Hi,

I have done something similar in the past but not quite the same as this.

I have a worksheet which has 8 different criteria I need to pivot on.

I have a front sheet with 8 combo boxes on it (for the end user to select
criteria) and need to pass these values to a pivot table, which in turn
outputs various graphs etc from the data returned by the pivot table. The
pivot table and graphs work fine but I don't know how to pass the combobox
values to the pivot table.

Any advice or pointers would be greatly appreciated.

Many thanks

 

[ryguy7272]

In one of my models I use a ListBox to do this; pretty much the same thing.
My data is in sheet named 'Summary' in Range A8:A31. I have this in C5:
=INDEX($A$8:$A$59,$B$7)

The Input Range on my ListBox is: $A$8:$A$59
The Cell Link is: B7 (notice the B7 in the Index function above).

Finally, I have some code like this (yours WILL be a bit different depending
on sheet names and cell references, but pretty much the same):

With
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With


Here's most of the code; again yours WILL be a bit different:
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select


With
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value),
"Count of ", xlCount

With
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With

HTH,
Ryan---

 

[Gary Brown]

Here's a sample of some code that should put you in the right direction:
'/---------------------------------------------------------
Public Sub aa_Test()
Dim iCount As Integer, i As Integer

On Error GoTo Exit_Sub

'identify the pivot table and field name in combo box
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
'get # of items in the pivotfield
iCount = .PivotItems.Count
're-set all items to visible
For i = 1 To iCount
.PivotItems(i).Visible = True
Next i
'turn off all items EXCEPT the one in the combobox
For i = 1 To iCount
If .PivotItems(i).Value <> _
Worksheets("Sheet1").ComboBox1.Value Then
.PivotItems(i).Visible = False
End If
Next i

End With
Exit_Sub:
End Sub
'/---------------------------------------------------------

 

[Steve Muir]

Got this working now, many thanks all