Invalid Procedure Call or Arguement When changing Page item in Pivot table

[lala14b]

Please help. I have recorded vb that changes a page item in a pivot table when selecting from a combo box - similar to this video.

http://www.datapigtechnologies.com/flashfiles/pivot10.html

The recorded code that vb gives me works only if it's selecting the selection that was chosen when recorded or if it was manually selected right before I choose the drop down item. Otherwise, it gives an invalid procedure call or arguement - runtime error 5.

This occurs in 2007 or compatibility mode. I think this is a new problem since it used to work okay for me.

Does anyone know what's going on??

 

[RoryA]

For column and row fields, you have to loop through every item in the field and set the visible property to True or False, unless you are using 2007 or later, in which case you can use filters.

 

[lala14b]

I am using 2007. I currently have it set up on the loop, but this isn't dynamic. I would like it to run based on my drop down selection as well. how do I use a filter? Or is it possible to make the visible property dynamic?

 

[RoryA]

Simplest thing is to record a macro while you filter a field to show what you want. Then simply replace the hardcoded value in the code with a reference to your dropdown.

 

[lala14b]

Hmm, i tried that but I can't seem to turn off the other items dynamically. If you have any other suggestions, I'd appreciate it!! Thanks again!

 

[RoryA]

What code did you try?

 

[lala14b]

When i record the macro, it won't let me handle 1 item (Q2) for some reason which is one issue. But the bigger issue, i believe is that I may be able to turn the quarter on by linking to a cell, but how do i turn off the other quarters I don't want to see.

Here are a few of my attemps and recordings:

Sub Quarter_Select()
'
' Period_Select Macro
'
Sheets("Business Services").Select
ActiveSheet.PivotTables("PivotTable1").PivotItems("Quarter").CurrentItem = CStr(Sheets("Start").Range("D11").Value)


End Sub

Sub Macro8()
'
' Macro8 Macro
'
'
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter")
.PivotItems("Q1").Visible = False
.PivotItems("Q3").Visible = False
.PivotItems("Q4").Visible = False
.PivotItems(Start!d11).Visible = True
End With
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarter")
.PivotItems("Q1").Visible = False
.PivotItems("Q2").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarter")
.PivotItems("Q1").Visible = False
.PivotItems("Q2").Visible = True
End With
End Sub

Sub Macro12()
'
' Macro12 Macro
'
'
Sheets("Refreshment").Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter")
.PivotItems("Q1").Visible = False
.PivotItems("Q2").Visible = True
End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarter")
.PivotItems("Q1").Visible = False
.PivotItems("Q2").Visible = True
End With
End Sub
Sub Macro13()
'
' Macro13 Macro
'
'
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarter")
.PivotItems("(SelectAll)").Visible = False
.PivotItems("Q3").Visible = True

End With
End Sub
Sub Macro14()
'
' Macro14 Macro
'
'
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter")
.PivotItems("Q1").Visible = True
.PivotItems("Q3").Visible = True
.PivotItems("Q4").Visible = True
.PivotItems("(blank)").Visible = True
End With
End Sub

(NOTICE Q2 doesn't appear, although it was included just like the rest).

 

[lala14b]

Ok, i'm much closer with this code:

ub Qtr_Select()

'
'
Clear_Quarter
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter")
.PivotItems("Start!d11").Visible = True


End With
End Sub
Sub Clear_Quarter()
Dim pt As PivotTable, pf As PivotField, pi As PivotItem
Dim B As String
B = Range("B1")
Set pt = Sheets("Business Services").PivotTables("Pivottable1")
Set pf = pt.PivotFields("quarter")
pt.PivotCache.Refresh
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
On Error Resume Next
For Each pi In pf.PivotItems
Select Case pi.Name
Case B
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
Set pf = Nothing
Set pt = Nothing
End Sub

But it won't work when i try to link it to a cell. Am i doing something wrong to reference it?

 

[RoryA]

This:

.PivotItems("Start!d11").Visible = True

shoudl be:

.PivotItems(Sheets("Start").Range("d11").value).Visible = True

You don't appear to have specified a worksheet when referring to the cell B1.

 

[lala14b]

Thanks so much - this worked.

SOOO CLOSE!

I was doing so good then tried to run the whole thing from the drop down, and got an invalid procedure call or argement again that is bombing on this peice:

Sheets("Rept Sch piv").Select
ActiveSheet.PivotTables("PivotTable9").PivotFields("Quarter").CurrentPage = CStr(Sheets("Start").Range("D11").Value)

End Sub

Any ideas? I can't thank you enough for all your help!!


The whole code looks like this:

Private Sub SelectPeriodBox_Change()
Refresh_All_Pivots
Period_Select



Sheets("Quarter").Select
Q1_Select

'*******************************************************************
Sheets("Business Services").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("Refreshment").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("AFS - B&I").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("Higher Ed").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("K-12").Select
Qtr_Select
Print_Area3
Range("A1:F1").Select

Sheets("Healthcare").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("Correctional").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("S&E").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("UK").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("Germany").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("Spain").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("Chile").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("Ireland").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("China").Select
Qtr_Select
Print_Area
Range("A1:F1").Select

Sheets("Other Intl").Select
Qtr_Select
Print_Area2
Range("A1:F1").Select

Sheets("Uniform").Select
Qtr_Select
Range("A1:F1").Select

Sheets("Wearguard").Select
Qtr_Select
Print_Area3
Range("A1:F1").Select


Sheets("Galls").Select
Qtr_Select
Print_Area3
Range("A1:F1").Select


End Sub

 

[RoryA]

I didn't think Quarter was a pagefield?