Creating a new chart- why Set NewChart = Charts.Add doesn't work?

[Thief_]

I've got the following code:

Charts.Add
Set NewChart = Charts.Add
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
With NewChart
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

The code fails at the line ".HasTitle = True". Viewing the NewChart object
in the Watch window show the object has NO properties, but I don't
understand why since it is dimmed as a chart object. The only way to make
this code work is to change it to:

Charts.Add
Set NewChart = ActiveChart
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
With NewChart
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

Can someone explain why this is so?


--
|
+-- Thief_
|

[Thief_]

Sorry, it seems that the line:

NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"

which moves the chart to a worksheet as an object, kills all the chart's
properties- this is so confusing!

--
|
+-- Thief_
|

"Thief_" <thief_@hotmail.com> wrote in message
news:e8HmidiRFHA.2252@TK2MSFTNGP15.phx.gbl...
> I've got the following code:
>
> Charts.Add
> Set NewChart = Charts.Add
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> The code fails at the line ".HasTitle = True". Viewing the NewChart object
> in the Watch window show the object has NO properties, but I don't
> understand why since it is dimmed as a chart object. The only way to make
> this code work is to change it to:
>
> Charts.Add
> Set NewChart = ActiveChart
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> Can someone explain why this is so?
>
>
> --
> |
> +-- Thief_
> |
>
>

[Andy Pope]

Hi,

I think it's that the NewChart object is a Chart when it's a chart sheet
but moving to the worksheet means its now a ChartObject, which has a
Chart reference. The variable does not automatically adjust itself.

Either format the chart and then move it or re reference NewChart after
you move it.

Set NewChart = Charts.Add
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
Set NewChart = ActiveChart
With NewChart
.ChartType = xlLineMarkers
.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

Cheers
Andy

Thief_ wrote:
> I've got the following code:
>
> Charts.Add
> Set NewChart = Charts.Add
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> The code fails at the line ".HasTitle = True". Viewing the NewChart object
> in the Watch window show the object has NO properties, but I don't
> understand why since it is dimmed as a chart object. The only way to make
> this code work is to change it to:
>
> Charts.Add
> Set NewChart = ActiveChart
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> Can someone explain why this is so?
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Jon Peltier]

Here's a simpler way. Make the chart object directly in the worksheet, without the
chart sheet as an intermediate step:

Set NewChart = ActiveSheet.ChartObjects.Add(100, 100, 300, 250).Chart

where the numbers in parentheses are the left, top, width, and height of the chart
object, in points.

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

Thief_ wrote:

> I've got the following code:
>
> Charts.Add
> Set NewChart = Charts.Add
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> The code fails at the line ".HasTitle = True". Viewing the NewChart object
> in the Watch window show the object has NO properties, but I don't
> understand why since it is dimmed as a chart object. The only way to make
> this code work is to change it to:
>
> Charts.Add
> Set NewChart = ActiveChart
> NewChart.ChartType = xlLineMarkers
> NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
> With NewChart
> .HasTitle = True
> .ChartTitle.Characters.Text = Title
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
> End With
>
> Can someone explain why this is so?
>
>

[Tushar Mehta]

No, it doesn't 'kill' the chart's properties. What happens is that
when the chart is moved to a worksheet, the object to which NewChart
points no longer exists. The correct way to relocate the chart and
continue to have a reference to it is to reestablish the reference to
the new object. Use

Set NewChart=NewChart.Location ( _
Where:=xlLocationAsObject, Name:="GraphResults")

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#ZbS7kiRFHA.3444@tk2msftngp13.phx.gbl>, thief_@hotmail.com
says...
> Sorry, it seems that the line:
>
> NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
>
> which moves the chart to a worksheet as an object, kills all the chart's
> properties- this is so confusing!
>
>

[Jon Peltier]

Or, as I try to encourage people to do, create it as a chart object to
begin with.

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


Tushar Mehta wrote:
> No, it doesn't 'kill' the chart's properties. What happens is that
> when the chart is moved to a worksheet, the object to which NewChart
> points no longer exists. The correct way to relocate the chart and
> continue to have a reference to it is to reestablish the reference to
> the new object. Use
>
> Set NewChart=NewChart.Location ( _
> Where:=xlLocationAsObject, Name:="GraphResults")
>

[Tushar Mehta]

Yeah, I know you like to start with a chartobject. I don't. Except in
some specific circumstances, I prefer to let XL decide on the dimensions
of the chartobject based on the current window size.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <Ohz5w2eSFHA.3156@TK2MSFTNGP15.phx.gbl>,
jonREMOVExlmvp@peltierCAPStech.com says...
> Or, as I try to encourage people to do, create it as a chart object to
> begin with.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Tushar Mehta wrote:
> > No, it doesn't 'kill' the chart's properties. What happens is that
> > when the chart is moved to a worksheet, the object to which NewChart
> > points no longer exists. The correct way to relocate the chart and
> > continue to have a reference to it is to reestablish the reference to
> > the new object. Use
> >
> > Set NewChart=NewChart.Location ( _
> > Where:=xlLocationAsObject, Name:="GraphResults")
> >
>

[Jon Peltier]

Hi Tushar -

That's fine if it's going to be an on-screen thing, but if it's intended for a
printed report, it's better to size it yourself. Besides, my retentive nature likes
charts that line up with the cell boundaries.

You can approximate the default Excel size if you make the chart half as wide and
tall as the active pane's usable size (subject to certain minimum values, of
course), centered within the range appearing in that pane.

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

Tushar Mehta wrote:

> Yeah, I know you like to start with a chartobject. I don't. Except in
> some specific circumstances, I prefer to let XL decide on the dimensions
> of the chartobject based on the current window size.
>