Working with Charts

[George Raft]

I'm working in Excel 97 on either Win 98 or XP. I want to create either an
embedded chart or a chart sheet depending on whether a checkbox
(chkNewSheet) on a user form (frmBoxPlot - yes, this is trying to automate
boxplots) is checked.

Here is the code I'm using:

If frmBoxPlot.chkNewSheet Then
Set chBoxPlot = Charts.Add
chBoxPlot.Location Where:=xlLocationAsNewSheet, Name:="BoxPlot"
chBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
chBoxPlot.ChartType = xlLineMarkers
Else 'user wants it on the current sheet
Set obBoxPlot = ActiveSheet.ChartObjects.Add(72, 72, 400, 300)
obBoxPlot.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
obBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
obBoxPlot.ChartType = xlLineMarkers
End If

obBoxPlot is type ChartObject, chBoxPlot is type Chart. rngOut is the data
range containing the series to be plotted.

Okay, here's what happens. If chkNewSheet is true, then Excel creates a new
sheet and populates the chart as expected. If chkNewSheet is false, it
opens an empty chart window on the active sheet.

What am I missing??

 

[Jon Peltier]

George -

A couple lines from the recorded macro don't really make sense in context of a well
written procedure. I've commented out these lines and inserted replacements:

If frmBoxPlot.chkNewSheet Then
Set chBoxPlot = Charts.Add
''chBoxPlot.Location Where:=xlLocationAsNewSheet, Name:="BoxPlot"
chBoxPlot.Name = "BoxPlot"
chBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
chBoxPlot.ChartType = xlLineMarkers
Else 'user wants it on the current sheet
Set obBoxPlot = ActiveSheet.ChartObjects.Add(72, 72, 400, 300)
''obBoxPlot.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
obBoxPlot.Name = ActiveSheet.Name ' not really useful
obBoxPlot.SetSourceData Source:=rngOut, PlotBy:=xlRows
obBoxPlot.ChartType = xlLineMarkers
End If

I have more VBA hints for charting here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

and my own little bit about box and whisker charts here:

http://peltiertech.com/Excel/Charts/statscharts.html#BoxWhisker

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______