Macros

[Tino]

Below is part of a macro to make a chart. I am running the macro in a
workbook with a single sheet in it. The macro runs correctly. However
"ActiveChart.SetSourceData" and "ActiveChart.Location" both refer to the
sheet by name. Instead of using the sheet name I would like to be able to
just refer to the sheet number, as I can do with "ChartTitle.Characters.Text
". I tried modifying both lines as shown below but both failed:

ActiveChart.SetSourceData" failed with "Object doesn't support this property
or method"
and
"ActiveChart.Location" failed with "Invalid procedure call or argument"

Is there a problem with the way I am referring to sheet(1) or is there
another way to be able to make the chart and store the chart in sheet(1)?


Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("QC_rad_pos_2").Range("D8:E200"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="QC_rad_pos_2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheets(1).Range("A1").Text
End With

ActiveChart.SetSourceData Source:=Sheets(1).Range("D8:E200"), _
PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheets(1)


regards

Peter

 

[mrice]

Is there any intervening code which has set the focus back onto th
sheet itself? If so, activechart won't be recognised - imagine wha
would happen if there were two on the sheet - which would it go for.

You might try either..

Name:=Sheets(1).Name

or ActiveChart.parent.Sheets(1).Nam

 

[Fred]

There is no intervening code. I posted the code exactly as I am using it
from the top down to .ChartTitle. After this there is more but there is no
problem with the remainder, only the bit at the start.

 

[mrice]

If you step throught the code line by line, you will see that the chart
is created first as a separate sheet and then embedded into sheet 1 as
a subsequent step. While it is a separate sheet, the chart itself is
sheet(1). You can see this by typing

? Sheets(1).name

into the immediate pane.

You can get round the problem by using sheets(2) which is the temporary
index of your data sheet.

I hope that this makes sense!

 

[Tino]

OK, I reordered my script slightly. By putting "ActiveChart.Location "
higher I was able to refer "SetSourceData" by sheet as shown:.

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.Location Where:=xlLocationAsObject, Name:="QC_rad_pos_2"
ActiveChart.SetSourceData Source:=Sheets(1).Range("D8:E200"),
PlotBy:=xlColumns


However I still can't refer to "ActiveChart.Location" as sheets(1). Whatever
I use in Name. doesn't work, eg Sheets(1), Sheets(2). Any other suggestion?


regards

Peter

 

[mrice]

I managed to get your code to work - can you try this

Sub Test()
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(2).Range("D8:E200"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheets(2).Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheets(1).Range("A1").Text
End With
End Sub

Workbook is attache

+-------------------------------------------------------------------
|Filename: Book1.zip
|Download: http://www.excelforum.com/attachment.php?postid=4854
+-------------------------------------------------------------------

 

[mrice]

Attachment


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4855 |
+-------------------------------------------------------------------+

 

[Tino]

I followed your link but I got the following message:

Invalid Attachment specified. If you followed a valid link, please notify
the administrator



I'll give your previous amendments a try.

 

[mrice]

If you drop me an e-mail address via my homepage, I'm happy to send th
workbook directly

 

[Tino]

I won't need to do that now. Your post with "ActiveChart.Location
Where:=xlLocationAsObject, Name:=Sheets(2).Name" worked fine. I have been
able to generalise enough of the macro now to do what I need to. Thanks for
your help.

regards

Peter