• 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.

How to update a (cube) slicer based on user input (VBA)

NJA

New Member
In my work I sometimes need to build dashboards based on pivot tables linked to cubes. To define common settings, like the last 12 months, I have been manually selecting them in a slicer called "slicerMonths".

Now I must to send the dashboard to managers, Excel-illiterate people that will only define the current month in a cell (ex: "November 2016", meaning the dashboard will show info from "November 2015" until "November 2016").

I have created a 13-cell area named "Last12m" where the months from Nov.2015 until Nov.2016 are shown. Is there a way with VBA to update the slicer selection with this information? -- all the examples I've seen so far were based on "regular" pivots, not cubes.

Many thanks.
 
NJA

Firstly, Welcome to the Chandoo.org Forums

Can you upload a sample file ?
 
Hello Hui, it's nice to be here.

I have now included an example file.
I think you won't be able to see the slicer properly, as it is linked to a cube connection, but at least you can understand what I wanted to explain in my initial post.
Note: at work I'm still using Excel 2010...

Again, many thanks.
 

Attachments

  • example.xlsm
    43.2 KB · Views: 6
Hi Chihiro,

I know it's my fault (I've never done VBA, just formulas), but I really need to see the code that would update this OLAP (cube) slicer in Excel 2010 based on the content of 13 cells (just like in the example.xlsm file) -- otherwise I get all sorts of errors that I cannot understand or correct.
Could you do me that favour?
 
Without knowing your data model hard to give you guidance....

Take a look at another method using cell range to set visible slicer items.
http://chandoo.org/forum/threads/se...the-cell-values-excel-2013.30553/#post-183004

For an example, if I'm loading Slicer from [Query1] and from [Month] column... string for each item would look like below.
"[Query1].[Month].&[October 2016]"

Edit: In your case, replace SlicerCaches with SlicerCacheLevels
 
Last edited:
Back
Top