Formatting Chart Data Series

[smpatty08]

I am trying to format a chart data series with VBA. I am having trouble having it edit the marker options and not the line options. I do not want a connecting line between multiple data point, but it keeps showing up. I have 2 data series in the same chart and one has the markers set to 100% transparency when not needed and my code sets the transparency to 0%, but then it has a line connecting all the data points. Here is the portion of code I'm working with.

            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.FullSeriesCollection(1).XValues = "='Enzyme Curve'!$G$12:$G$16"
            ActiveChart.FullSeriesCollection(1).Values = "='Enzyme Curve'!$J$12:$J$16"
            ActiveChart.FullSeriesCollection(2).XValues = "='Enzyme Curve'!$G$17:$G$18"
            ActiveChart.FullSeriesCollection(2).Values = "='Enzyme Curve'!$J$17:$J$18"
            ActiveChart.FullSeriesCollection(2).MarkerStyle = xlMarkerStyleX
            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.FullSeriesCollection(2).Select
[COLOR=#ff0000]            With Selection.Format.Line[/COLOR]
[COLOR=#ff0000]                .Transparency = 0[/COLOR]
[COLOR=#ff0000]                .ForeColor.ObjectThemeColor = msoThemeColorAccent2[/COLOR]
[COLOR=#ff0000]                .ForeColor.TintAndShade = 0[/COLOR]
[COLOR=#ff0000]                .ForeColor.Brightness = 0[/COLOR]
[COLOR=#ff0000]            End With[/COLOR]
            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.ChartArea.Copy
            Sheets("Quadratic work").Select
            Range("B38").Select
            ActiveSheet.Paste
            ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
            With Selection
                .Type = xlPolynomial
                .Order = 2
            End With
            Range("C5:E5").ClearContents
            Range("C5").FormulaR1C1 = "=LINEST('Enzyme Curve'!R12C10:R16C10,'Enzyme Curve'!R12C7:R16C7^{1,2})"
            Range("C5:E5").FormulaArray = _
                "=LINEST('Enzyme Curve'!R12C10:R16C10,'Enzyme Curve'!R12C7:R16C7^{1,2})"
            Range("B7").Activate

Any help would be appreciated as this is driving me crazy.

 

[smpatty08]

Figured it out after many many google searches. The code below will remove the connecting line between data points.

With Selection
     .Border.ColorIndex = xlNone
End With

Here it is incorporated within my code from the OP.

            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.FullSeriesCollection(1).XValues = "='Enzyme Curve'!$G$12:$G$16"
            ActiveChart.FullSeriesCollection(1).Values = "='Enzyme Curve'!$J$12:$J$16"
            ActiveChart.FullSeriesCollection(2).XValues = "='Enzyme Curve'!$G$17:$G$18"
            ActiveChart.FullSeriesCollection(2).Values = "='Enzyme Curve'!$J$17:$J$18"
            ActiveChart.FullSeriesCollection(2).MarkerStyle = xlMarkerStyleX
            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.FullSeriesCollection(2).Select
            With Selection.Format.Line
                .Visible = msoTrue
                .Transparency = 0
                .ForeColor.ObjectThemeColor = msoThemeColorAccent2
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
            End With
            With Selection
                .Border.ColorIndex = xlNone
            End With
            ActiveSheet.ChartObjects("Chart 2").Activate
            ActiveChart.ChartArea.Copy
            Sheets("Quadratic work").Select
            Range("B38").Select
            ActiveSheet.Paste
            ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
            With Selection
                .Type = xlPolynomial
                .Order = 2
            End With
            Range("C5:E5").ClearContents
            Range("C5").FormulaR1C1 = "=LINEST('Enzyme Curve'!R12C10:R16C10,'Enzyme Curve'!R12C7:R16C7^{1,2})"
            Range("C5:E5").FormulaArray = _
                "=LINEST('Enzyme Curve'!R12C10:R16C10,'Enzyme Curve'!R12C7:R16C7^{1,2})"
            Range("B7").Activate

Still not sure why it use

.Border.ColorIndex

when "Border" is clearly listed under the "Marker" options when performing the same task by hand, but it does the trick.

 

[Jon Peltier]

The formatting of series lines and markers is messed up in VBA. Series.Format.Line formats both the connecting lines and the marker outlines the same, and there is no good way to separate them. You can use MarkerForegroundColor and MarkerBackgroundColor for the colors, but line thicknesses are confounded.