BubbleChart XP vs VISTA
 

Hello,

I have a dynamically generated bubblechart that I populate from data from a
pivot table. This works perfectly on my XP computer (Office 2003), but on the
Vista computer of my boss (Office 2007), the thing gives me an error message
and I just can't figure it out...

Here is the line of code where it throws the error:

ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

The error message is:

"Run-time error '5':

Invalid procedure call or argument"

As stated, the code works fine on Office 2003, but not Office 2007, anyone
know what the problem is?

Sincerely,

Stitch10925

BubbleChart XP vs VISTA
 

Hi Stitch,

I tried running a slightly simplified statement in Excel 2007 and it worked
fine (on Vista).

ActiveChart.SeriesCollection(1).BubbleSizes = _
"=" & Sheets("Sheet1").Cells(1, 5).Address( _
ReferenceStyle:=xlR1C1, External:=True)

Perhaps the error is in the Cnt variable?

Ed Ferrero
www.edferrero.com

"Stitch10925" wrote in message
...
Hello,

I have a dynamically generated bubblechart that I populate from data from
a
pivot table. This works perfectly on my XP computer (Office 2003), but on
the
Vista computer of my boss (Office 2007), the thing gives me an error
message
and I just can't figure it out...

Here is the line of code where it throws the error:

ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

The error message is:

"Run-time error '5':

Invalid procedure call or argument"

As stated, the code works fine on Office 2003, but not Office 2007, anyone
know what the problem is?

Sincerely,

Stitch10925

BubbleChart XP vs VISTA
 

Several years ago I posted this routine that created a bubble chart from a
selected four-column range on the worksheet. I just tested it in 2007, and
only had to make a minor change in the part that removed excess series from
the initially constructed chart. The rest works fine.

Sub OneRowPerBubbleSeries()
'' Takes 4-column range and constructs Bubble chart
'' Uses one series per row: Columns in order: Name, X, Y, Z
Dim wks As Worksheet
Dim cht As Chart
Dim srs As Series
Dim rng As Range
Dim rng1 As Range
Dim rownum As Integer
Dim bFirstRow As Boolean
Set wks = ActiveSheet
Set rng = Selection
Set cht = wks.ChartObjects.Add(100, 100, 350, 225).Chart
bFirstRow = True
For rownum = 1 To rng.Rows.Count
Set rng1 = rng.Cells(rownum, 2).Resize(1, 3)
If IsNumeric(rng1.Cells(1, 1).Value) And _
IsNumeric(rng1.Cells(1, 2).Value) And _
IsNumeric(rng1.Cells(1, 3).Value) Then
'' First time: need to do it differently
If bFirstRow Then
cht.SetSourceData Source:=rng1, _
PlotBy:=xlColumns
cht.ChartType = xlBubble
bFirstRow = False
'' Remove spurious excess series
Do Until cht.SeriesCollection.Count = 1
cht.SeriesCollection(cht.SeriesCollection.Count).D elete
Loop
Else
Set srs = cht.SeriesCollection.NewSeries
End If
With cht.SeriesCollection(cht.SeriesCollection.Count)
.Values = rng1.Cells(1, 2)
.XValues = rng1.Cells(1, 1)
.BubbleSizes = "=" & rng1.Cells(1, 3).Address _
(ReferenceStyle:=xlR1C1, external:=True)
.Name = rng.Cells(rownum, 1)
End With
End If
Next
End Sub

The reason your routine might be failing is if your counter variable is
equal to one.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Stitch10925" wrote in message
...
Hello,

I have a dynamically generated bubblechart that I populate from data from
a
pivot table. This works perfectly on my XP computer (Office 2003), but on
the
Vista computer of my boss (Office 2007), the thing gives me an error
message
and I just can't figure it out...

Here is the line of code where it throws the error:

ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

The error message is:

"Run-time error '5':

Invalid procedure call or argument"

As stated, the code works fine on Office 2003, but not Office 2007, anyone
know what the problem is?

Sincerely,

Stitch10925

BubbleChart XP vs VISTA
 

I found the problem. It was not the Cnt variable, this was working fine. I
should have posted more code for you guys, but I figured it out anyways. Here
is the solution:

This is what I had befo

Do Until Cnt = RowCnt

ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(Cnt - 1).Name =
Sheets("BubbleData").Cells(Cnt, 1).Value
ActiveChart.SeriesCollection(Cnt - 1).XValues =
Sheets("BubbleData").Cells(Cnt, 2).Value
ActiveChart.SeriesCollection(Cnt - 1).Values =
Sheets("BubbleData").Cells(Cnt, 3).Value
ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

Cnt = Cnt + 1
Loop

ActiveChart.ChartType = xlBubble

This works fine under Office 2003, however, you need to change the location
of the "ActiveChart.ChartType = xlBubble" line in order for it to work under
Office 2007...but then it does not work under Office 2003 anymore. Here is
the code for office 2007:

Cnt = 2
Do Until Cnt = RowCnt

ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(Cnt - 1).Name =
Sheets("BubbleData").Cells(Cnt, 1).Value
ActiveChart.SeriesCollection(Cnt - 1).XValues =
Sheets("BubbleData").Cells(Cnt, 2).Value
ActiveChart.SeriesCollection(Cnt - 1).Values =
Sheets("BubbleData").Cells(Cnt, 3).Value

ActiveChart.ChartType = xlBubble

ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

Cnt = Cnt + 1
Loop

Thanks for your help guys!

Greets,

Stitch10925

---------------------

"Stitch10925" wrote:

Hello,

I have a dynamically generated bubblechart that I populate from data from a
pivot table. This works perfectly on my XP computer (Office 2003), but on the
Vista computer of my boss (Office 2007), the thing gives me an error message
and I just can't figure it out...

Here is the line of code where it throws the error:

ActiveChart.SeriesCollection(Cnt - 1).BubbleSizes = "=" &
Sheets("BubbleData").Cells(Cnt, 4).Address(ReferenceStyle:=xlR1C1,
External:=True)

The error message is:

"Run-time error '5':

Invalid procedure call or argument"

As stated, the code works fine on Office 2003, but not Office 2007, anyone
know what the problem is?

Sincerely,

Stitch10925