User Defined Chart Declaration VBA

[Bench]

After much searching i managed to find a list of ways to declare in VBA the type of chart you like, eg. xlCylinderBarClustered, xlXYScatterLines, xlLineStacked, xlPie etc, how though do i declare my user defined charts? or is there also a way that you can play with the formatting of the chart colour and appearance etc. For example one of my fave charts uses coloured outlines for the bars and white centres, i always like a white background etc etc

Any help would be gratefully received

Thanks

 

[Jon Peltier]

To save your own custom chart type, right click on the chart, select Save As Template, and use the dialog to save a new chart template.

In code it looks like:

    ActiveChart.SaveChartTemplate "C:\Users\Jon Peltier\AppData\Roaming\Microsoft\Templates\Charts\MrExcelChart.crtx"

The path in the command above is the official place to store templates where Excel will find them. Change my name to your Windows user name.

To apply your custom template to a chart, right click the chart, choose Change Chart Type, click on the Templates folder, and select the template to use.

In code:

    ActiveChart.ApplyChartTemplate "C:\Users\Jon Peltier\AppData\Roaming\Microsoft\Templates\Charts\MrExcelChart.crtx"

To create a new chart using your template, select the data, go to the Insert tab, click the little icon at the bottom right corner of the Charts group ("See All Charts"), click on All Charts, click on the Templates folder, and select your template.

In code, you have to create a chart using any chart type, then apply the code as above to change to your template:

    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.ApplyChartTemplate "C:\Users\Jon Peltier\AppData\Roaming\Microsoft\Templates\Charts\MrExcelChart.crtx"
    ActiveChart.SetSourceData Source:=Range("Sheet2!$B$2:$C$9")

Change Sheet2!B2:C9 to your data range.