Excel chart markers - making them programmatically

[RobBroekhuis]

I have some code I use to create custom legends for charts. Very useful if the series names have super/subscripts, symbols, etc, since legend entries in excel are plain text. This code works fine with most of my charts, which have just lines, no markers. Now I need to use it for some charts with markers, and I'm wondering what the easiest way is to create a marker shape programmatically. I could make a huge select case statement and generate all the various shapes individually, but I'm really hoping there's a better way...
I'd appreciate any insights.
Rob

 

[lambuhere1]

Rob

Probably this should guide you ..

For Each chxa In ActiveWorkbook.Charts
chxa.Activate
no_of_legends = ActiveChart.Legend.LegendEntries.Count
For i = 1 To no_of_legends - 1
ActiveChart.Legend.LegendEntries(i).LegendKey.Select
With Selection
.MarkerBackgroundColorIndex = Activeworkbook.Worksheets("Sheet2&quot.Cells(i, 1).Value
.MarkerForegroundColorIndex = Activeworkbook.Worksheets("Sheet2&quot.Cells(i, 1).Value
.MarkerSize = 5
End With

Next i

Next chxa

I use the above to change legend colors. You can slightly modify the code to include all your marker styles in Sheet2.

Ram P

 

[RobBroekhuis]

Ram,
Thanks for your reply. It correctly addresses how I might change the marker characteristics (style, color) on a chart, including its legend. However, what I'm trying to do is create a marker from scratch, to put in a custom legend (i.e., not one generated by Excel, but built from scratch by code). I'm starting to think there is no easy way of going about this...
Rob