global formatting of legend keys

[Yossi evenzur]

Hi
In a chart i have multiple legend keys, as a result all colors are not
visible because the columns are thin and border line black, thus i can see
only black columns. i can format each key manually but if i have 200 keys it
would never end, how can change the border option from automatic to none?

 

[Andy Pope]

Hi,

Quickest way would be via code.

Sub x()

Dim objSeries As Series

With ActiveChart
For Each objSeries In .SeriesCollection
objSeries.Border.LineStyle = xlNone
Next
End With

End Sub

That said a chart with 200 legend entries is not really going to be
readable.
Excel colours are limited to 56 and even that is too much in 1 chart.

Maybe you need to reconsider your chart.

Cheers
Andy

 

[Peter T]

Try this macro -

Sub NoSeriesBorders()
Dim cht As Chart
Dim sr As Series

On Error Resume Next
Set cht = ActiveChart
On Error GoTo 0

If cht Is Nothing Then
MsgBox "No Chart is selected"
Exit Sub
End If

For Each sr In cht.SeriesCollection
sr.Border.ColorIndex = xlNone
' or
' sr.Border.LineStyle = xlNone
Next

End Sub

There's no undo but you could change xlNone to xlAutomatic to reset borders

Regards,
Peter T

 

[Yossi evenzur]

Try this macro -

Sub NoSeriesBorders()
Dim cht As Chart
Dim sr As Series

On Error Resume Next
Set cht = ActiveChart
On Error GoTo 0

If cht Is Nothing Then
MsgBox "No Chart is selected"
Exit Sub
End If

For Each sr In cht.SeriesCollection
sr.Border.ColorIndex = xlNone
' or
' sr.Border.LineStyle = xlNone
Next

End Sub

There's no undo but you could change xlNone to xlAutomatic to reset borders

Regards,
Peter T




every time i wan't to do this i have to create a macro?

 

[Peter T]

Hi Andy,

Excel colours are limited to 56 and even that is too much in 1 chart.

FWIW starting in the 57th series a two colour 50% pattern is 'automatically'
applied for the next 56, then a different %grey in the following 56 an so
on. In theory a different 'apparent' colour can be applied in each fill up
to max 255 series. Might want to customize the 10 duplicate colours in a
default palette.

I agree with your point though.

Regards,
Peter T

 

[Yossi evenzur]

Hi
First thanks for the help, now, usually i try to avoid this but in this case
i had too, but maybe you can offer a better solution, as an analogy, say you
have 200 students and test scores in math and their age and you need to find
the worst performing student with cross correlation to his age, i thought
that by putting this on a 3D chart will enable me to pinpoint the most likely
candidates, what you think?

 

[Jon Peltier]

3D charts sound like a great solution, but 3D chart rendering on a 2D medium
(paper or monitor) will always be limited. A 3D column chart with 200 points
would be horrific.

I would carry out my analysis with a few charts. I'd use an XY chart with
age on the X axis and test score on the Y. I would use a labeling routine to
put the student names or other ID on the points as data labels, but for 200
students, it would probably be too cluttered. I'd also make a couple dot
plots, both with student name down the left axis, one with test score and
the other with age plotted horizontally, both plotted in descending order of
score or age. Probability plots of the test scores and ages would also be
helpful to show whether any apparently outlying points were truly out of
distribution.

Chart data labeling routines:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

Dot Plots:

http://peltiertech.com/Excel/Charts/DotPlot.html

Probability Plots:

http://peltiertech.com/Excel/Charts/ProbabilityChart.html

- Jon

 

[Jon Peltier]

Post on top, as is customary in these groups, to make it easier to follow
the thread chronologically.

every time i wan't to do this i have to create a macro?

Put this macro into your Personal.xls workbook, add a button to a toolbar
somewhere, and attach the macro to the button. It will always be available.

- Jon