Using VBA Changing line cap type for X-Y chart

[nagovind]

While constructing the chart using VBA it is creating the chart with round type ends

to change the same it is required to change the Line cap type as SQUARE


But the below code is not working


PLEASE ADVISE the right code


Sub SetLineCap()


ActiveChart.SeriesCollection(1).Select

With Application

.SendKeys ("^1") ' format dialog

.SendKeys ("{DOWN}{DOWN}{DOWN}{DOWN}") ' Line style

.SendKeys ("{TAB}{TAB}{TAB}{TAB}") ' Cap type...... I NEED SQUARE TYPE

.SendKeys ("F{RETURN}") ' Flat

.SendKeys ("{ESC}") ' close dialog

End With


End Sub

 

[Hui]

Nagovind


You have come across one of the areas in Excel where the Object Model isn't complete.

There is no way using VBA to set the Cap Type property as you've found


I have read about using Send Keys as you have suggested

This will require that you use the exact same set of key strokes that you press when you do it manually.

This may change between Excel 2007/10 and now 13 as the Dialogs are all different


Andy Pope has a smalls comment here on it:

http://www.excelforum.com/excel-charting-and-pivots/724682-changing-line-cap-type-for-x-y-chart.html

 

[nagovind]

Thanks Hui

Yes i believe only manual commands will do the job

thanks

 

[trbot]

I created an account just to post this. It turns out you CAN programmatically set a square cap type with VBA for Excel 2007 using a little hack.

The following will set the first series in the active chart to have square caps.

    ActiveChart.SeriesCollection(1).Select
    With Selection.Format.Line
            .DashStyle = msoLineSquareDot
            .DashStyle = msoLineSolid
    End With

Cheers.

 

[MichaelLane]

Hi trbot,

Thanks so much for the tip.... by chance you know the hack to set a flat cap instead of a square cap?

Thanks!

 

[NARAYANK991]

Hi ,

Can you upload a file with the chart in it ?

Narayan