Macro that changes line styles based on the series' name

[trumptmast]

Hi,

I have a question regarding changing the line styles in a graph with a macro.

There are several series in the graph, however, they are named either "SOT_*" or "EOT_*" . * being the rest of the series name.

Currently, all of the lines are solid. But I wish to change all the series with the prefix EOT to dashed and leave the others as they are.

How do I create a macro loop code that looks for the prefix of the series' name and change the line style based on the prefix?

Thanks a bunch!

[Tony James]

trumptmast wrote:

> Hi,
>
> I have a question regarding changing the line styles in a graph with a
> macro.
>
> There are several series in the graph, however, they are named either
> "SOT_*" or "EOT_*" . * being the rest of the series name.
>
> Currently, all of the lines are solid. But I wish to change all the
> series with the prefix EOT to dashed and leave the others as they are.
>
> How do I create a macro loop code that looks for the prefix of the
> series' name and change the line style based on the prefix?
>
> Thanks a bunch!
>
>
> --
> trumptmast

When doing something like this I use the macro recorder to see what
objects Excel uses. So with a chart on sheet1 I started the recorder
and double-clicked one of the series lines in question and continued
with the Format Data Series dialog.

It turns out that it uses the SeriesCollection Collection Object, so
with that information it was quite easy to code this macro:

Sub FormatDataSeries()
Dim myChart As SeriesCollection
Dim mySeries As Series

Set myChart = Worksheets(1).ChartObjects(1).Chart.SeriesCollection

For Each mySeries In myChart
Debug.Print mySeries.Name
If Left(mySeries.Name, 4) = "EOT_" Then
mySeries.Border.LineStyle = xlDash
End If
Next
End Sub

hth, Tony

[trumptmast]

Works quite well. Thanks a lot!