macro for bubble chart

[Vesset]

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

 

[Tushar Mehta]

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:


--
Regards,

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

 

[Tushar Mehta]

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




--
Regards,

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

 

[Jon Peltier]

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

 

[Guest]

THANKS!!

-----Original Message-----
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




--
Regards,

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