Trouble with assigning a Click Action to a dynamically created Chart

[Kamu]

Hi all,

I have the following problem.

I have a fully dynamic VBA generated Spreadsheet.
It import raw data, sort them into Pivot Table then generate Graph in a newly generated Sheet.
Now here is my problem, I need to add a click action for when I click on a chart, the spreadsheet automatically move to the proper tab.

I tried to do it via Macro but while I get the code, if I change it, excel doesn't take my modification.
I would like to keep button as a last solution as I don't want to pollute my graphs with extra object.

By the way, is there any way to "fix" a chart to the Sheet so user can't move them accidentally? And obviously not impact on the function above?

Thank you in advance.

[Norie]

Can you post the code?

[Kamu]

Hi Norie,

Sorry for the delay.
I can't really speak of code right now but basically the following:
ActiveSheet.ChartObjects("Chart1").Activate
Selection.OnAction = "Book1!ThisWorkbook.unHideSR"

I presume that when I create the Macro to record the action:
- Select the Chart
- Assign Macro to Chart
Excel is effectively assigning the macro, so if click on the chart, the macro run and unhide the tab as wanted.
But if I change the Macro assigned to the chart then original macro continue running.

And as my code requires to link the Macro for the Charts dynamically, this is not going anywhere for me.
Either Excel doesn't allow for this type of action or I'm looking at the wrong place.

Regards