VBA to select value in slicer based on shape name

Hi
In the attached workbook, I have made an easy excample of what I'm trying to solve.

I have a front page with shapes that will control the slicers in the other page. So if I press "USA", or "CA", it will choose that shape's name (or caption) in the slicer/filter value.

The table is in the datamodel, and only as an "connection".

I believe the country shapes need one macro, and the state-shapes a different macro, since it is two different slicers.

I have tried so many VBA versions, without luck.

Can someone try to make this work, thanks?

Assign sample macros to the corresponding shapes.

Please Login or Register  to view this content.

Artik

Thanks for your suggestion. Unfortunately, your solution requires me to create a unique macro for each of the shapes. In the file I'm going to use, I will have a lot of shapes like countries, so I need a vba that gets the name of the shape that calls the macro (alternatively, gets the text in the shape that calls the macro). Then all the "country"-shapes can have the same vba.

Remember this attached file is only a simple example.

I don't see any major problems. For the "State" shapes, create slightly different names so that there is a link between State and Country.
We assign one macro to all shapes

Please Login or Register  to view this content.

Artik

Yeah, that works! Thank you for your quick and precise reply.

Appreciate if you would answer the following questions as well:
1. How does the ubound work in this vba?
2. If I would add the next level "store_city" under state. Which solution would you recommend then?

Thanks again

Originally Posted by eivstei

1. How does the ubound work in this vba?

Application.Caller returns the name of the clicked object.
Split(s, "-") creates an array by splitting the name into elements. In this case, the separator is a hyphen. The array formed by splitting the string is always indexed from 0.
UBound(v) returns the upper index of the array (LBound - the lower index).
In the following conditions, I check what the upper index is. If 0, it means that basically no division has taken place (there is no separator), which would indicate that it is the highest level - Country. If Ubound returns 1 it means that we are dealing with State.

Originally Posted by eivstei

2. If I would add the next level "store_city" under state. Which solution would you recommend then?

On a similar basis as before. You create an object name "with an access path", e.g. "USA-CO-Denver". You add another condition. It would probably look like this

Please Login or Register  to view this content.

Artik

Thanks Artik!

Hi again Artik!
I tried your last code with several levels (country-state-city-..), and it works perfectly.

I turn to you again hoping that you can help me with one more thing. I have attached the latest version of file.

I want a headline in the Dashboard sheet (textbox, label or similar).
I need it to contain the same value as choosen in the slicer.
Since I have three slicers in this case, it need to be the last changed value in one of the three slicers.

As an Alternative B solution if the other is difficult, I can have a textbox for each slicer.

No matter which shape you click, its name is always the last element of the "path". v(Ubound(v)) will return that last element to you.
Change the code snippet according to the following:

Please Login or Register  to view this content.

Artik

Yes, that works nice when user click the shapes. I forgot to mention that the users also can click directly in the slicers, and therfore I need a code that everytime a change in one of the slicers, the last choosen value should appear in the textbox. Is that possible?

I suggest starting a new thread because what you are asking significantly goes beyond the topic of the current thread.
In general, it will be about creating a responsive slicer event that.... doesn't exist. But smart people have come up with something. Take a look at Jan Karel Pieterse.

Artik