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).Delete
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" <(E-Mail Removed)> wrote in message
news:69448B27-019F-4FA1-9347-(E-Mail Removed)...
> 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
Similar Threads
