Hi Peter,

Thanks, will have a look.

RBS

"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I was just about to post some example stuff when and your follow-up
appeared
answering your own question. But you might as well have it any way -

Sub test()
Dim ws As Worksheet
Dim ch As Chart
Dim chObj As ChartObject
Dim r As Range
Set ws = ActiveSheet

Set r = ws.Range("b2")

Set ch = ws.ChartObjects.Add(r.Left, r.Top, 200, 120).Chart

With ch
.ChartType = xlColumnClustered
.SetSourceData ws.Range("A1:b5"), xlColumns
.Parent.Name = ("MyCart1")
.HasTitle = True
.ChartTitle.Text = .Parent.Name
End With

With ws.ChartObjects.Add(300, 50, 200, 120)
.Name = "MyChart2"
With .Chart
.ChartArea.AutoScaleFont = False
'.ChartArea.Font.Size = 10
.ChartType = xlColumnClustered
.SetSourceData ws.Range("A1:b5"), xlColumns
.HasTitle = True
.ChartTitle.Text = .Parent.Name
.Parent.Activate
.ChartArea.Select
End With
End With

On Error Resume Next
Set ch = ActiveChart
If Not ch Is Nothing Then
MsgBox ch.Parent.Name
End If

With ws.ChartObjects("MyCart1")
.Activate
.Chart.ChartArea.Select
End With

Set ch = ActiveChart
If Not ch Is Nothing Then
MsgBox ch.Parent.Name
End If
End Sub

I don't think you need .Location when adding a chart if adapting the
above,
or need to select anything.

Re your comments about unexpected sizing, note .ChartArea.AutoScaleFont =
False in the above, which you can reset later.

Regards,
Peter T



"RB Smissaert" wrote in message
...
OK, this works:

Set objChart = Charts.Add

With objChart
.Location xlLocationAsObject, strSheetName
End With

With ActiveChart
.SetSourceData _
Source:=rngRange, _
PlotBy:=xlColumns
.ChartType = xlColumnClustered
.HasTitle = True
.ChartTitle.Characters.Text = strName
.HasLegend = False
End With

Understand now that the second argument of .Location has to be an
existing
sheet.

Now the strange thing is that this works fine in my first range, but if I
run the same
on a different range I get no proper columns anymore, but very thin
lines,
although it is
still the column type of chart. This other range has the same kind of
data
as the first range.

RBS


"RB Smissaert" wrote in message
...
When I record making a chart embedded in the sheet I get this:

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("testsheet"). _
Range("J3:K14"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"testsheet"

But this doesn't work as it falls over at the line where the location
is
set:

Set objChart = Charts.Add

With objChart
.Location xlLocationAsObject, strName
.ChartType = xlColumnClustered
.SetSourceData _
Source:=rngRange, _
PlotBy:=xlColumns
End With

The error will be:
runtime error 1004, method location of object chart failed.
A chart has already been made, but not this is a chart in a new
chartsheet
and I don't want that.
How should this be done?


RBS