Want to run an event on manually selecting a chart on a sheet

[Kevlar1]

Hi All, having some issues and don't have any more hair to pull out! I have 31 charts on a sheet and on manually selecting one want to run some script.

I need to be able to use the chart number selected on the sheet. I have played with this for to long and tried using worksheet change and selection change events but no joy. Can anyone give me a leader?

Many thanks in advance for any help.

 

[Jon Peltier]

You can't just select the chart manually, then run your macro, using ActiveChart to identify the chart?

 

[Kevlar1]

Thanks for replying, much appreciated. I think the thing im struggling with is where to trigger the script from as struggling with getting it to work from worksheet selectionchange.

 

[Jon Peltier]

Selecting a chart will not fire a worksheet event.

If you're manually selecting the sheet, and it's for your own use, what difference is it if you also then manually run the code?

Or is it for others to use? If the code is in the same workbook, it might be reliable enough to assign a macro to the chart.

Right click on the chart, choose Assign Macro, and select the VBA procedure from the list that pops up.

An example of the code you might write is below. I simply cycled through three chart types so I could watch the charts change as I clicked on them. Application.Caller is the name of whatever triggered the macro, in this case the name of the Shape (ChartObject) that you clicked on to run the code. So the chart that you clicked on is referenced by ActiveSheet.ChartObjects(Application.Caller).Chart.

Sub DoChart()
  With ActiveSheet.ChartObjects(Application.Caller).Chart
    Select Case .ChartType
      Case xlLineMarkers
        .ChartType = xlColumnClustered
      Case xlColumnClustered
        .ChartType = xlArea
      Case xlArea
        .ChartType = xlLineMarkers
    End Select
  End With
End Sub

 

[Jon Peltier]

Another approach would be to set up chart events in the worksheet so that clicking a chart would trigger the code. This is much more complicated, but much more powerful, because you could perform different actions based on what was selected (an axis, a title, a plotted series). I wrote a tutorial about this recently, Chart Events in Microsoft Excel.

 

[Kevlar1]

Cheers, this is most helpful. Thank you very much for the steer.