Call macro depending on cell link of a Form Control Box

Hi all,

Can anyone help me simplify below code? I'm trying to run a certain Macro depending on which prefecture was selected within my Form Control Combo Box. Though, I don't want to write 48 macros for every (!) prefecture in my list. Is there an easier way to do it?

Please Login or Register  to view this content.

Maybe make the code more flexible and have it check the prefecture name from the Form Control cell link? All macros are the same, only the prefecture shape name changes...

Please Login or Register  to view this content.

'run time error 13' Type mismatch...

If I type

Please Login or Register  to view this content.

instead, then the first part works but

Please Login or Register  to view this content.

is highlighted and the error 'The item with the specific name wasn't found' appears...

code.PNG

Still doesn't seem to work. Though, I think I know why...

'str' is referring to the Form Control value, rather than its name, correct me if I'm wrong? Though, I need the code to look for the shape name e.g. "Hokkaido" rather than shape name "9".
I have just included an Index formula to get the currently selected prefecture

Please Login or Register  to view this content.

where X48 is the Form Control cell link and W48:W95 the prefecture list

and have named the range "Selected_Prefecture"

How would the second part of the code based on the above look like? Something like the below?

Please Login or Register  to view this content.

I'm using a Form Control ComboBox as mentioned above, so I guess your code suggestion does not apply?

That works!! Thanks!