asked on 3/27/2007

Activesheet instead of sheet1.

Hello group,

Instead of :

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(strSelectedRange), PlotBy:=xl3DColumn

I like to use:

ActiveChart.SetSourceData Source:=ACTIVESHEET.Range(strSelectedRange), PlotBy:=xl3DColumn

but it the VB compiler complains that this doesn't support the method !!!

How can I solve this limitation? maybe I'm using a WorkBook that doesn't have sheet1 or it is renamed. Then what?!

Regards,
ak

Microsoft Office Microsoft Applications Microsoft Excel

Last Comment

akohan

3/28/2007

p912s

3/27/2007

What if you use this:
ActiveWorkbook.ActiveSheet

Instead of this:
ActiveSheet

p912s

3/27/2007

Or maybe:
Selection.Address

Instead of:
ACTIVESHEET.Range(strSelectedRange)

p912s

3/27/2007

>>strSelectedRange
Where does this come from?

If the above didn't help; can you post the rest of the code?

akohan

3/27/2007

ASKER

Hi,

Thanks but neither one works. It complains that "Object Doesn't support this property or method".

regards,
ak

p912s

3/27/2007

>> It complains that "Object Doesn't support this property or method".
Which is the same as your original error. Both are valid statements depending on how they're used.

Can you post your spreadsheet? You can upload here in a zipped format - http://ee-stuff.com/

Please post the link that is provided after the upload.

byundt

3/27/2007

Is this a chart embedded on a worksheet (in which case your code should work) or a separate chartsheet? If the latter, then the activesheet is a chartsheet, which has no range property.

I tested the following code with an embedded chart on a worksheet. I selected the chart, then ran the sub:

Sub ChangeChart()
Dim strSelectedRange As String
strSelectedRange = "H8:I9"
ActiveChart.SetSourceData Source:=ActiveSheet.Range(strSelectedRange), PlotBy:=xl3DColumn
End Sub

JasonRubens

3/27/2007

What about

Dim mySheet as Worksheet
Set mySheet = activesheet

and then
ActiveChart.SetSourceData Source:=mySheet.Range(strSelectedRange), PlotBy:=xl3DColumn

ASKER CERTIFIED SOLUTION

akohan

3/28/2007

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

akohan

3/28/2007

ASKER

Thanks!