Slicer Lookalike in VBA

[chrisp28103]

Hi,

I am currently trying to build a slicer that is connected to multiple pivot tables. However, these pivot tables contain grouped fields so an ordinary slicer will not work.

That being said, I am attempting to build a slicer lookalike in VBA that can accomplish slicer like capabilities (changing a filter on multiple pivot tables that contain grouped fields)

I reached out to one excel expert, Jan Karel Pieterse, and he recommended that I break this problem down into smaller chunks. He stated that a good starting place would be to get the code that can "get all (visible) items from a pivot table field using VBA."

Thanks for any and all help!

-Chris

 

[Jerry Sullivan]

Hi Chris,

Here's a link to a thread that might be of some help in your quest.

It provides some code examples for filtering multiple PivotTables with an OLAP data source using VBA instead of Slicers.
http://www.mrexcel.com/forum/excel-...c-applications-code-update-pivot-filters.html

In the previous thread in which Jan offered you the suggestion to build your own slicer lookalike using VBA, you wrote....

Basically I need to be able to filter multiple pivot tables that are sourcing OLAP data and using grouped fields based on the date. Eventually this data will be going onto a SharePoint site, so it would be nice if I could filter the date of all of the tables from a single place. As you mentioned slicers will not be able to achieve this, but I am wondering if there are any other functions within excel that could give me the desired result.

I've only had a little bit of experience with Excel's Office 365 WebApp on SharePoint, but as far as I know it doesn't support VBA.
So even if you reach your objective of a slicer lookalike that uses VBA, that might not meet your needs.

You could potentially have users download the workbook and use Excel 2010 or later client application if you need to use VBA.

If you decide to go with an approach that uses VBA, consider using a real slicer that is linked to a dummy PivotTable instead of trying to build your own object.
You could use VBA to populate the dummy PivotTable with the (grouped) dates from the real PivotTables as well as updating the real PivotTables whenever the dummy PivotTable's slicer is changed.

 

[chrisp28103]

Thanks for the advice Jerry!

I will definitely keep your post in mind if I run into problems with the SharePoint site later down the road.

However, I believe I have figured out how to create the slicer lookalike. I was able to write and assign a macro to a combobox that does the job I needed.

Thanks again!

Chris