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

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

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

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