Microsoft Access 2007 Chartspace Help

Question

I have a form with a default view of a chart. This form is loaded as a subform, the data is dynamically filtered by the parent form. It comes up and looks way better than using a chart object (when the form scales the chart object it looks really bad).

My question is this. I have been able to find little information on the chartspace object model which appears to be the way to pragmatically modify the underlying form, I can change the line colors and marker colors but need to find out how to remove the connecting line in a line graph and change a single markers color. This is what I have so far:

Private Sub Form_Load()

Dim XChart as Object
set XChart = Me.ChartSpace.Charts(0)

With XChart.SeriesCollection(0)

  .interior.color = vbBlue
  .line.color = xlNone   ' This does not work, it makes a light blue color line
  .line.weight = 0  ' still shows a thin line

End With

Any documentation on this for Office 2007 would be very helpful, I have been searching for hours and can only find one page on MSDN that basically say's nothing on the OM for ChartSpace. There are a few 2000/2003 OWC component examples which have been able to get me this far but not able to take me any further, apparently the OWC DLL was phased out in Office 2007 also..

Answer 1

Using the Excel 2010 Macro Recorder I was able to expose the VBA code needed to do this, previous versions of Office did not record the code that would take care of this task.