• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Control Pivot Table filter via Dropdown Box VBA

Dokat

Member
I use below VBA code to control Manufacturer field of the "Dropdown" pivot table via drop down box. Based on the selection from drop down box pivot table manufacturer field should change. Code used tow ork perfectly but its giving me error since i changed the pivot table and highlights below row in yellow. Can you please advise

ActiveSheet.PivotTables("Dropdown").PivotFields("Manufacturer").ClearAllFilters

upload_2017-1-10_14-52-3.png

Code:
Sub Dropdown()

'

'Dropdown Macro
'

'
Sheets("POS Tracker").Select
Sheets("POS Tracker").Visible = True
Sheets("Source").Visible = True
Sheets("Source").Select
ActiveSheet.PivotTables("Dropdown").PivotFields("Manufacturer").ClearAllFilters
ActiveSheet.PivotTables("Dropdown").PivotFields("Manufacturer").CurrentPage = "(All)"
ActiveSheet.PivotTables("Dropdown").PivotFields("Manufactuer").CurrentPage = Range("C226").Text
Sheets("Source").Visible = False


End Sub
 
Hi ,

The only situation in which this can happen is if the pivot table does not have a pivot field labelled Manufacturer.

Narayan
 
Is below line a misspelling? ("Manufactuer" instead of "Manufacturer")

Code:
ActiveSheet.PivotTables("Dropdown").PivotFields("Manufactuer").CurrentPage = Range("C226").Text
 
No it's not mispelling Manufacturer...I made a typo and corrected it in the code. But still getting the same error message. i recorded the macro and get the same code. Not sure whats going on here

I also tried "ActiveSheet.PivotTables("Dropdown").ClearAllFilters" and still no luck.

Here is what my pivot table looks like....I am trying to change the field where it says "All" to the value on its right...in this case "The Clorox Company"

upload_2017-1-11_8-35-58.png
 
This works in Excel 2010, 2013 and 2016
Code:
  ActiveSheet.PivotTables("Dropdown").PivotFields("Manufacturer").ClearAllFilters
 
Still getting the same error message with

Code:
ActiveSheet.PivotTables("Dropdown").PivotFields("Manufacturer").ClearAllFilters
 
You are loading PivotTable from Data model. You need different syntax for PivotField name.

You need something like below.
Code:
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").ClearAllFilters
 
When i do that it still gives me Run Time Error '1004': "Unable to set the CurrentPage property of the Pivotfield class" error message and highlights below in yellow


Code:
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").CurrentPage = "(All)"
 
Last edited:
You can't use .CurrentPage for pivottables made from Data Model/OLAP cube.

Like I stated in the other thread. You use ".VisibleItemsList" property of PivotField.

Code:
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").VisibleItemsList = Array("[Append].[Manufacturer].&[THE CLOROX COMPANY]")
 
I modified the code to read as below...Is this makes sense?

Code:
Sub Dropdown()

Sheets("POS Tracker").Select
Sheets("POS Tracker").Visible = True
Sheets("Source").Visible = True
Sheets("Source").Select
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").VisibleItemsList = "(All)"
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").VisibleItemsList = Range("C114").Text
Sheets("Source").Visible = False

End Sub
 
No. Syntax is wrong.

You can't use "THE CLOROX COMPANY" alone.
You need it like "[Append].[Manufacturer].&[THE CLOROX COMPANY]".
And since it is a list, you need to nest it in Array() or it will throw error.

So code becomes...

Code:
Sub Dropdown()

Dim vItem As String

Sheets("POS Tracker").Select
Sheets("POS Tracker").Visible = True
Sheets("Source").Visible = True
Sheets("Source").Select
ActiveSheet.PivotTables("PivotTable8").ClearAllFilters
vItem = "[Append].[Manufacturer].&[" & Range("C114").Text & "]"
ActiveSheet.PivotTables("PivotTable8").PivotFields("[Append].[Manufacturer].[Manufacturer]").VisibleItemsList = Array(vItem)
Sheets("Source").Visible = False

End Sub
 
Thank you it worked...so you created reference for Array and than used array rather than range().text. I can not stress enough how much of a help you guys have been and l learned so much since joining the forum
 
.so you created reference for Array and than used array rather than range().text.

Not exactly. I constructed string using Range().Text (i.e. vItem variable).
Then put it in Array() function to convert to single item array.
 
Syntax for OLAP/Data Model based PivotFields.
[QueryName].[ColumnName].[ColumnName]
(IE. All items in column)

Syntax for Pivot Item from same source.
[QueryName].[ColumnName].&[ItemName]
(IE. Single child/item in column)

You can also write VBA code to find out the name of available fields.
 
Back
Top