Pivot Table Report Filter matching

[Mikeykt]

Hi

I'm trying to connect my Pivot table report filter to a corresponding combo box on a front page.

There are five boxes and report filters with matching names, I've tried the following code but I keep getting 'Object doesn't support this property'

Can anyome help?

Sub pivotchoice()
'
' pivotchoice Macro
'
Worksheets("GIFTS").Select
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign").CurrentPage = Worksheets("FrontSheet").CampaignNameGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Code").CurrentPage = Worksheets("FrontSheet").CampaignNumGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Campaign_Year").CurrentPage = Worksheets("FrontSheet").CampaignYearGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Can_Mail").CurrentPage = Worksheets("FrontSheet").CanMailGCombo.Value
ActiveSheet.PivotTables("Gifts").PivotFields("Can_Phone").CurrentPage = Worksheets("FrontSheet").CanPhoneGCombo.Value
End Sub

 

[Andrew Poulsom]

What code do you get if you record a macro while changing the current page in the Campaign pivot field? Are your ComboBoxes from the Control Toolbox?

 

[Mikeykt]

Hi

Yes I created the Campaign combo box from the control field, should I create it using VBA instead?

 

[Andrew Poulsom]

I don't know what "the control field" is, but did you record a macro? If so please post it.

 

[Mikeykt]

Hi

Ok this is the one for the Campaign Name

Sub CampaignNameG_Change()
'
' CampaignNameG_Change Macro
'
'
Sheets("GIFTS").Select
ActiveSheet.PivotTables("GIFTS").PivotFields("CAMPAIGN").ClearAllFilters
ActiveSheet.PivotTables("GIFTS").PivotFields("CAMPAIGN").CurrentPage = "(All)"
End Sub

Thanks

 

[Andrew Poulsom]

What does this tell you?

Sub Test()
    MsgBox Worksheets("FrontSheet").CampaignNameGCombo.Value
End Sub

 

[Mikeykt]

Hi

It's still coming back with a runtime error 438

 

[Andrew Poulsom]

Hi

It's still coming back with a runtime error 438

Which means that the ComboBox isn't on that sheet or it's not from the Control Toolbox. If you right click it do you get a pop up menu?

 

[Mikeykt]

Yes I do.

With the options of Assign Macro and Format Controls

 

[Andrew Poulsom]

So it's from the Forms Toolbar. You won't be able to use its Value property because that returns an index. This worked for me:

Sub Test()
    With Worksheets("FrontSheet").DropDowns("CampaignNameGCombo")
        MsgBox .List(.ListIndex)
    End With
End Sub