CODEWITHSUNDEEP
vbaexcelcharts
Mr.Burns
Mr.Burns

Reputation: 700

VBA Excel 2010 - Run code when selecting chart legend

Currently what I am trying to do I don't know if it is even possible and I dont even know where to begin (I have tried this code but I am unable to get it to even work).

I am trying to get a macro to run automacticly when clicking the legend of a chart, I know you can assign a macro to charts to run them that way but if possible I would like to do this from the charts legend. The code I would like to run is below, it is a simple toggle to turn the chart lines on/off

If ActiveChart.SeriesCollection(1).Format.Line.Visible = msoTrue Then
    ActiveChart.SeriesCollection(1).Format.Line.Visible = msoFalse
ElseIf ActiveChart.SeriesCollection(1).Format.Line.Visible = msoFalse Then
    ActiveChart.SeriesCollection(1).Format.Line.Visible = msoTrue
End If

If this isnt possible I can honestly see why but if there are any ideas on work arounds that to would be great

Edit:

I made a small mistake in what I was looking to do here, sorry, I would like the code to run when clicking an legend entry, this is the text part of the legend next to the coloured line (so Chart line 1, Chart line 1 etc etc), hopefully this clarifys it

Upvotes: 0

Views: 658

Answers (2)

Axel Richter
Axel Richter

Reputation: 61862

This assumes, you have your embedded chart object as the first chart object in the first worksheet.

I have the following in Sheet1: enter image description here

Create a class module. Name it clsChartEvents. In that class module put the following code:

Public WithEvents myChart As Chart

Private Sub myChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

 Dim ElementID As Long, Arg1 As Long, Arg2 As Long

 With myChart

  .GetChartElement x, y, ElementID, Arg1, Arg2

  If ElementID = xlLegendEntry Then
   If .SeriesCollection(Arg1).Format.Line.Visible = msoTrue Then
    .SeriesCollection(Arg1).Format.Line.Visible = msoFalse
   ElseIf .SeriesCollection(Arg1).Format.Line.Visible = msoFalse Then
    .SeriesCollection(Arg1).Format.Line.Visible = msoTrue
   End If
   .ClearToMatchStyle
  End If

 End With

End Sub

.GetChartElement x, y, ElementID, Arg1, Arg2 gets the ElementID, Arg1 and Arg2 from the mouse click position x, y.

In a default module put the following code:

Dim myChartWithEvents As clsChartEvents

Sub init()
 Set myChartWithEvents = New clsChartEvents
 Set oChart = ThisWorkbook.Worksheets(1).ChartObjects(1).Chart
 Set myChartWithEvents.myChart = oChart
End Sub

Now run the sub init and click the legend entries.

A click on the legend entry should switch the corresponding line on/off.

Upvotes: 1

Jochen
Jochen

Reputation: 1254

As an easy workaround you could draw a rectangle-form over the legend. You can format that rectangle with no interior and no line making it invisible and even group chart and rectangle so that both can be moved around together.

You can link that rectangle to you macro then. In your macro you can't use 'ActiveChart' but have to identify the correct chart with name or id. Hope this helps.

Upvotes: 1

Related Questions