Excel VBA - How do you set line style for chart se

问题:

I would like the plot lines in a chart to be either solid, circle dot, or square dot based upon a certain criteria specified by the user. I can successfully set the line color and marker style for the plot using a macro, but I cannot seem to find the object which holds the value for the plot line style property. I have tried using the record macro function, but changing the line style in the properties windows does not show up in the code, and running the recorded macro has no effect.

Any help is greatly appreciated!

回答1:

Create a chart with 255 data series, run the code (and do other formatting as necessary). Then save it as a template.

Sub dd()

Dim wb As Workbook
Set wb = Application.ActiveWorkbook

Dim myChart As Chart
Set myChart = wb.Charts("Chart5")

Dim mySeries As series

For Each mySeries In myChart.SeriesCollection
    mySeries.Format.Line.Weight = 1#
Next

End Sub

回答2:

YourChartSeries.Border.LineStyle = [some value from the XlLineStyle enumeration]

UPDATE: recording in XL 2010 I get this -

ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineSysDot
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineSysDash
End With

Which might be what you're looking for.

回答3:

If you have a line chart, I ended up creating a switch statement because I couldn't figure out how to make an array of "Name" variables. See list of line types here

For j = i To num_lines_to_plot
        count = count + 1
        ActiveChart.SeriesCollection.NewSeries
        With ActiveChart.SeriesCollection(j)
            .Name = _
                "='"**... (you'll need to fill this in)**
            'create gradient
            .Format.Line.ForeColor.RGB = RGB(255, 20 * count, 0)
            'modify linetype
            If count > 4 Then
                line_type = count Mod 4
            Else
                line_type = count
            End If

            Select Case line_type
                Case Is = 1
                    .Format.Line.DashStyle = msoLineSolid
                Case Is = 2
                    .Format.Line.DashStyle = msoLineSquareDot
                Case Is = 3
                    .Format.Line.DashStyle = msoLineDash
                Case Is = 4
                    .Format.Line.DashStyle = msoLineLongDash
                End Select

        End With
    Next