Specifying a Template Chart Type in Excel 2007:-

This is a great technique for saving time when you are creating a chart with a lot of custom formatting. A VBA macro can make use of a custom chart template, provided that you plan on distributing the custom chart template to each person who will run your macro.

In Excel 2007, you save custom chart types as .crtx files and stored them in the %appdata%\Microsoft\Templates\Charts\ folder.

To apply a custom chart type, you use the following:

ActiveChart.ApplyChartTemplate (“MyChart.crtx”)

If the chart template does not exist, VBA returns an error. If you would like Excel to simply continue without displaying a debug error, you can turn on an error handler before the code and turn it back on when you are done. Here’s how you do that:

On Error Resume Next

ActiveChart.ApplyChartTemplate (“MyChart.crtx”)

On Error GoTo 0 ‘ that final character is a zero