Excel VBA in Excel 07 Line Style Cap Type

[NFuller]

I have a macro that will edit the color of a line in a chart. I would like the width of this line to be ~20pt.

I want the Cap Type for this line to be flat. By "Cap Type" I am referring to the Line Style option that you can find when you right click on the line in chart and select "Format Data Series".


Here is my problem. I setup the chart to look exactly as I would like it to initially. A 20pt line with flat ends. When I run this simple macro, the color changes, but the end caps revert back to the round default. I cannot find any way to prevent this from happening or modify the cap type via VBA.


Here is the code that I am using in the macro:

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Border.Color = 3

​

I am hoping that someone here may have found a way to get around this.

 

[daveydoom]

Try recording the macro (I believe it's in Tools > Macro > Record new macro)and format your data the way you want it. When you're done, stop the recording and go back and view the source code. You can then modify things as required (if required) .