Working with Charts yet again

[George Raft]

I'm back again with a follow up question on manipulating charts and their
series in VBA (Excel 97).

The following code is not doing what I'm expecting:
(chBoxPlot is type Chart, rngOut is type Range, frmBoxPlot is a userform and
chkEmbedded is a checkbox control)

Set chBoxPlot = Charts.Add
chBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
chBoxPlot.ChartType = xlLineMarkers

If frmBoxPlot.chkEmbedded Then 'user wants embedded chart
Set chBoxPlot = chBoxPlot.Location(Where:=xlLocationAsObject,
Name:=SheetName)
End If

' *** This is where things break down - nothing happens

For Var = 1 To NumVars
chBoxPlot.SeriesCollection(Var).LineStyle = xlNone
Next Var

With chBoxPlot.ChartGroups(1)
.HasDropLines = False
.HasHiLoLines = True
.HasUpDownBars = True
.GapWidth = 150
End With

The code creates the chart as expected, but the For Loop and With - End With
block do no do what I expect; i.e., they do not remove the lines connecting
the points nor produce HiLines and LowBars on the series.

And while I'm at it, why can't I use the .ChartGroups command to replace the
For Loop? In other words, since I want to operate on all the series in the
same way, shouldn't including .LineStyle = xlNone within the With/End With
block work?

And another while I'm at it: Any recommendations on a reference
manual/guide - I have the Wrox Excel 2003 VBA, but I'm not convinced it's
the best thing for me.

By the way Jon, if you're monitoring - I couldn't find your reply, but thank
you for taking the time to email me.

Tony (george raft is just such a great name, I couldn't resist it.)

 

[Peter T]

Hi Jeff

' *** This is where things break down - nothing happens

For Var = 1 To NumVars
chBoxPlot.SeriesCollection(Var).LineStyle = xlNone
Next Var

try:
chBoxPlot.SeriesCollection(Var).Border.LineStyle = xlNone

that would make a line type chart invisible! (you defined ChartType =
xlLineMarkers)

I assume "NumVars" is defined somehow

Regards,
Peter

 

[Jon Peltier]

I'm back again with a follow up question on manipulating charts and their
series in VBA (Excel 97).

The following code is not doing what I'm expecting:
(chBoxPlot is type Chart, rngOut is type Range, frmBoxPlot is a userform and
chkEmbedded is a checkbox control)

Set chBoxPlot = Charts.Add
chBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
chBoxPlot.ChartType = xlLineMarkers

If frmBoxPlot.chkEmbedded Then 'user wants embedded chart
Set chBoxPlot = chBoxPlot.Location(Where:=xlLocationAsObject,
Name:=SheetName)
End If

' *** This is where things break down - nothing happens

Step through the code, use the Locals window to check your variables. Also, do the
series chart the data you expect them to?

For Var = 1 To NumVars
chBoxPlot.SeriesCollection(Var).LineStyle = xlNone
Next Var

SeriesCollection(Var).LineStyle didn't raise an error?

With chBoxPlot.ChartGroups(1)
.HasDropLines = False
.HasHiLoLines = True
.HasUpDownBars = True
.GapWidth = 150
End With

The code creates the chart as expected, but the For Loop and With - End With
block do no do what I expect; i.e., they do not remove the lines connecting
the points nor produce HiLines and LowBars on the series.

How many series do you have?

And while I'm at it, why can't I use the .ChartGroups command to replace the
For Loop? In other words, since I want to operate on all the series in the
same way, shouldn't including .LineStyle = xlNone within the With/End With
block work?

The ChartGroup object has no Border or LineStyle members.