I certainly do not pretend to be a chart expert! I'd answered your question in the database thread, but it is mo' better to ask and answer here.

My guess is the ChartWizard method may help, also check MSDN article Q147803. Code snippet:

Dim TheChart As Chart
Set TheChart = ActiveSheet.ChartObjects(1).Chart
' reformat Chart1 as line chart, adds legend, category and value axis titles.
TheChart.ChartWizard Gallery:=xlLine, HasLegend:=True, CategoryTitle:="X-axis", ValueTitle:="Y-Axis"