Macros

  • Thread starter Thread starter Tino
  • Start date Start date
T

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
 
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
 
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.
 
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!
 
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
 
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
+-------------------------------------------------------------------
 
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.
 
If you drop me an e-mail address via my homepage, I'm happy to send th
workbook directly
 
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
 
Back
Top