[RESOLVED] Excel VBA - how to remove chart series marker line color

[VBAhack]

I can easily remove the border of the marker using the Excel menus (Format Data Series, Marker Line Color, No Line) but can't seem to do it using VBA. Using the recorder results in no change at all. I've tried changing .MarkerBackgroundColor = xlNone and that doesn't work either. What am I missing? I've been playing with this way too long and still can't figure it out.

[vbfbryce]

I don't do a whole lot with charts, but when I create a marker line chart, then do the Format Data Series, Marker Line Color, No Line...I don't see it changing anything as far as I can tell. What is it supposed to be doing? And what version of Excel are you using?

[VBAhack]

Excel 2010. Marker line is an outline around the marker. For example, you can have a red fill and a black outline. I want to turn off the outline but can't seem to figure out how to do it w/ VBA. In older versions of Excel it was called marker background color, but things appear to be different in newer excel versions. The odd thing is the macro recorder produces the same code for turning off the line connecting the markers and turning off the marker border (line). Clearly they are different. I'm beginning to think it can't be done via VBA. Disappointing.............

[vbfbryce]

Might have come up with a "poor man's solution."

This code will make the border the same color as the fill:

Sub mark3()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim ser As Series
    
    Set ws = ActiveSheet
    Set cht = ws.ChartObjects(1)
    Set ser = cht.Chart.SeriesCollection(1)
    
    ser.MarkerForegroundColor = ser.MarkerBackgroundColor
End Sub

[VBAhack]

Thanks, I've already done that. It essentially means the marker is larger than desired. I still want to turn off the marker line. Seems possible looking at the object model, but simply doesn't work.

Any chart power users out there?

[vbfbryce]

so make the marker size smaller:

ser.MarkerSize = 8

[vbfbryce]

I believe you have to access "points" rather than just "series" as I previously did:

Sub mark3()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim ser As Series
    Dim pts As Points
    Dim i As Integer
    
    Set ws = ActiveSheet
    Set cht = ws.ChartObjects(1)
    Set ser = cht.Chart.SeriesCollection(1)
    Set pts = ser.Points

    For i = 1 To pts.Count
        pts(i).MarkerForegroundColor = xlNone
    Next i
End Sub

[VBAhack]

Actually, I found a way to do it for the entire series instead of point by point. The trick was to use the value -2 (xlHundreds or xlParamTypeBinary) instead of -4142 (xlNone) for the MarkerForegroundColor. I discovered it by turning off the marker line using the excel dialog boxes, then running a macro to tell me the marker foreground color. Also, I'd been fiddling with the wrong item - background color instead of foreground color. Thanks for your inspiration!

What really goofed me up was the macro recorder. It wasn't even close.