Charting Weirdness and VBA

[Terry Detrie]

I have a chart with 9 series, and macro that will make them hidden or
visible, depending on the contents of a range of 9 cells in a
worksheet ("IR-Graph")

An odd thing happens sometimes with the the 9th series. I get an run
time error 1004 (Unable to get property of LegendEntry class).
Through a lot of trial and error, I found that this error only occurs
with the 3rd series is turned 'on'.

Since both cases in the IF...THEN block have multiple actions on the
LegendEntries, I turned off Series #3 and stepped through the code.
After first line clears (either case) I know Series #9 exists and
there's no error. Then I turn Series #3 off, which *shouldn't* affect
Series#9. When I step into next line of code I get the error. When I
turn Series #3 back off, I'm able to advance to next line of code
again.

I'm usually pretty good at debugging but this is too weird. Help!



Sub Graph_Legend(oCht As Chart)
Dim Counter As Integer
Application.ScreenUpdating = False

With oCht
For Counter = 1 To 9

If Sheets("IR-Graph").Cells(Counter + 2, 1).Value = 0 Then
' Turn graph line off
.Legend.LegendEntries(Counter).LegendKey.Border.LineStyle =
xlNone
.Legend.LegendEntries(Counter).Font.ColorIndex = 2
Else
' Turn graph line on
.Legend.LegendEntries(Counter).Font.ColorIndex = xlAutomatic
.Legend.LegendEntries(Counter).LegendKey.Border.ColorIndex =
xlAutomatic
.Legend.LegendEntries(Counter).LegendKey.Border.Weight =
xlMedium
End If
Next Counter
End With

Application.ScreenUpdating = True
End Sub

Terry Detrie

 

[Markus Grein]

I have a chart with 9 series, and macro that will make them hidden or
visible, depending on the contents of a range of 9 cells in a
worksheet ("IR-Graph")

An odd thing happens sometimes with the the 9th series. I get an run
time error 1004 (Unable to get property of LegendEntry class).
Through a lot of trial and error, I found that this error only occurs
with the 3rd series is turned 'on'.

....

Your code works fine on my end. Created a workbook and took over your VBA
code,
and - as far as I can judge - it does the job nicely.

Feel free to send me copy of your worksheet - maybe there's something about
the way the
chart object is actually addressed / handed over to the macro.

cheers,
Markus

 

[Sandy V]

Hi Terri,

As it did for Markus your code works fine for me, to be
effective I needed to change the legendentry background
transparency.

Are all the "types" your series of the same ChartGroup,
eg BarGroups, LineGroups etc. Are they all on the same
category axis. Any trend lines. Have you ever deleted and
inserted a new series. I assume you simply have a line or
XY scatter, so the latter might be a possibility in your
case.

The point is the Series collection does not necessarily
correlate in index order with the Legend series, even
though it typically does.

If you think this is a possibility it gets complicated to
relate them. And just for reference (doubt applies to
you) if a legend entry has been user deleted it's
virtually impossible to do so with certainty, I've spent
many hours trying!

Regards,
Sandy

-----Original Message-----

I have a chart with 9 series, and macro that will make them hidden or
visible, depending on the contents of a range of 9 cells in a
worksheet ("IR-Graph")

An odd thing happens sometimes with the the 9th series. I get an run
time error 1004 (Unable to get property of LegendEntry class).
Through a lot of trial and error, I found that this error only occurs
with the 3rd series is turned 'on'.

Since both cases in the IF...THEN block have multiple actions on the
LegendEntries, I turned off Series #3 and stepped through the code.
After first line clears (either case) I know Series #9 exists and
there's no error. Then I turn Series #3 off, which *shouldn't* affect
Series#9. When I step into next line of code I get the error. When I
turn Series #3 back off, I'm able to advance to next line of code
again.

I'm usually pretty good at debugging but this is too weird. Help!



Sub Graph_Legend(oCht As Chart)
Dim Counter As Integer
Application.ScreenUpdating = False

With oCht
For Counter = 1 To 9

If Sheets("IR-Graph").Cells(Counter + 2, 1).Value = 0 Then
' Turn graph line off
.Legend.LegendEntries

(Counter).LegendKey.Border.LineStyle =

xlNone
.Legend.LegendEntries(Counter).Font.ColorIndex = 2
Else
' Turn graph line on
.Legend.LegendEntries(Counter).Font.ColorIndex = xlAutomatic
.Legend.LegendEntries

(Counter).LegendKey.Border.ColorIndex =

xlAutomatic
.Legend.LegendEntries

(Counter).LegendKey.Border.Weight =