macro for bubble chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vesset - 25 Dec 2003 22:07 GMT

When I record the following code through macro recorder,
then the bubble chart comes out fine. However, when I then
try to rerun the same code then I get an error message:

Run-time error '1004'
Method 'ChartType' of object '_Chart' failed

Any advice on why the xlBubble as chart type is not
working?

Sub Macro2()
Charts.Add
ActiveChart.ChartType = xlBubble
ActiveChart.SetSourceData Source:=Sheets
("Sheet2").Range("A2:C6"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject,
Name:="Sheet2"
End Sub

Reply to this Message

Tushar Mehta - 26 Dec 2003 03:23 GMT

Signature

Regards,

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

> When I record the following code through macro recorder,
> then the bubble chart comes out fine. However, when I then
[quoted text clipped - 15 lines]
> Name:="Sheet2"
> End Sub

Reply to this Message

Tushar Mehta - 26 Dec 2003 03:25 GMT

This is one of those instances when the macro recorder code doesn't
work directly. The Chart Wizard makes 'adjustments' based on the
current region of the active cell. That 'adjustment' is not available
through the code. Use either of the foll. workarounds:

Option Explicit
Sub Macro2()
Range("B3").CurrentRegion.Select
Charts.Add
ActiveChart.ChartType = xlBubble
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C4"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
Sub Macro3()
Charts.Add
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C4"), _
PlotBy:=xlColumns
ActiveChart.ChartType = xlBubble
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:C4"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

Signature

Regards,

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

> When I record the following code through macro recorder,
> then the bubble chart comes out fine. However, when I then
[quoted text clipped - 15 lines]
> Name:="Sheet2"
> End Sub

Reply to this Message

Jon Peltier - 28 Dec 2003 06:35 GMT

The problem is that the macro recorder always puts the chart type step
ahead of the source data step. You can't define a bubble chart without
data yet, because it is unforgiving if you do not have three ranges (X,
Y, Bubble Size), whereas other chart types can fake it with no data yet
defined. Tushar's solutions both put the chart type assignment after
the source data definition.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

> This is one of those instances when the macro recorder code doesn't
> work directly. The Chart Wizard makes 'adjustments' based on the
[quoted text clipped - 22 lines]
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> End Sub

Reply to this Message

- 03 Jan 2004 02:40 GMT

THANKS!!

>-----Original Message-----
>This is one of those instances when the macro recorder code doesn't
[quoted text clipped - 45 lines]
>>
>.

Reply to this Message