VBA Error: Unable to set the visible property of the PivotItem Class

[drewmey]

I am trying to provide my users the ability to automatically select what data they want visible within a Pivot Table (estimators trying to find pieces of the estimate from the entire estimate explained later). The way I would like them to do this, is to type the items they want shown in a column. This column uses an offset to set the items as a named range array. They press a button once, only item #1 in that column is shown. They press it again, only item #2 is shown. When they get to the end, it goes back to showing all of the data.

For example, one column in the pivot table is "Notes". The user enters "Alternate #1" in the "Notes" column for every applicable item. Then the Pivot Table could show all items related to Alternate #1 in the Pivot Table if they were to type "Alternate #1" in the provided column and press my button.

My problem is that I get the error when trying to set all pivot items to visible. Error is "Unable to set the visible property of the PivotItem Class"

Please Login or Register  to view this content.

Thanks for an help.

[drewmey]

Hmm, never mind. For some reason it is now working with the code I have shown. I'll have to test some more to see if I can replicate my errors.

[rkey]

what you describe sound like a Slider, have you tried that one yet?

[drewmey]

Unless I am missing something, I am not sure how a slider would be implemented in my scenario. I thought they were used to change numbers from Min to Max in the increment provided.

However, I am trying to filter the results of hundreds of lines that are reported to a Pivot Table by having the user list the categories they would like to query for in a column. Then toggle through those queries one by one.

Could you explain how to use a slider for this?

[rkey]

If you have the column in your pivot, you can select the pivot and choose insert slider from the pivot menu and bind it to the column

[drewmey]

Oooh you mean slicer? I'll try that but the notes column that I'm contemplating using can have tons of different entries. Could be very cumbersome looking.