Chart series formating in VBA 1

[scotttom]

Oh GAWD do I hate charting in access.

So I have two series of data that I want to format differently. Works great in design view and when I look at the chart object in MS Graph, (one series line, the second just dots) but when I look at it in an actual report I get it with both series line. It's driving me crazy and I've invested WAY too much time in this. Here is how I am populating the data and I suspect I'm missing something here.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim oChart As Graph.Chart 'make sure you have a reference set to MsGraph
   Dim oSheet As Graph.DataSheet

   Set oChart = Me.Graph4.Object 'graph object named Graph1

   Set oSheet = oChart.Application.DataSheet

   'clear old data
   oSheet.Cells.ClearContents
   

   

   'Load data to graph '
   'first row is for Legend entries, you'll have to look at your data to
 'figure what to enter here
 'US, Europe, Japan, Latin America, Other,
        oSheet.Cells(1, 2) = Me!R1
        oSheet.Cells(2, 2) = Me!d60E2
        oSheet.Cells(3, 2) = Me!L60e2
        oSheet.Cells(1, 3) = Me!R2
        oSheet.Cells(2, 3) = Me!d24E3
        oSheet.Cells(3, 3) = Me!L24e3
        oSheet.Cells(1, 4) = Me!R3
        oSheet.Cells(2, 4) = Me!d96E3
        oSheet.Cells(3, 4) = Me!L96e3
        oSheet.Cells(1, 5) = Me!R4
        oSheet.Cells(2, 5) = Me!d38E4
        oSheet.Cells(3, 5) = Me!L38e4
        oSheet.Cells(1, 6) = Me!R5
        oSheet.Cells(2, 6) = Me!d15E5
        oSheet.Cells(3, 6) = Me!L15e5
        oSheet.Cells(1, 7) = Me!R6
        oSheet.Cells(2, 7) = Me!d61E5
        oSheet.Cells(3, 7) = Me!L61e5
        oSheet.Cells(1, 8) = Me!R7
        oSheet.Cells(2, 8) = Me!d25E6
        oSheet.Cells(3, 8) = Me!L25e6
 
   oChart.ChartType = xlLine

   'just to make sure

   Set oSheet = Nothing
   Set oChart = Nothing
End Sub

What can I do to make the second series just dots.

Thanks in advance for any help.

Scott

 

[ByteMyzer]

After the line:

oChart.ChartType = xlLine

...insert this line:

oChart.SeriesCollection(2).Border.LineStyle = xlDot

 

[scotttom]

ok almost there. I just want the datapoints not the connecting line. Thanks in advance.

Best,

Scott

 

[ByteMyzer]

Ahh, you said you wanted "just dots". I took that to mean a dotted line, not the plot points without the line. In that case, the code should be revised from:

oChart.SeriesCollection(2).Border.LineStyle = xlDot

...to:

oChart.SeriesCollection(2).Border.LineStyle = xlNone
oChart.SeriesCollection(2).MarkerStyle = xlAutomatic
' NOTE: Other possible values for .MarkerStyle:
' xlSquare
' xlDiamond
' xlTriangle
' xlX
' xlStar
' xlDot
' xlDash
' xlCircle
' xlPlus
' xlPicture

 

[scotttom]

Excellent information.

So Now I am unable to see the Diamonds what is the syntax to control the color of the datapoints? I found the size but not the color.

Thanks again.

Scott

 

[ByteMyzer]

Background Color:
oChart.SeriesCollection(2).MarkerBackgroundColor

Foreground Color:
oChart.SeriesCollection(2).MarkerForegroundColor


It might do you well to do some studying of the Excel objects in Visual Basic for Applications. Everything relating to chart object manipulation is in there.