vba to apply border is skipping first series

[paul100]

Hi

im using the following macro to generate a bubble chart. the only issue I have is the .Format.Line.Visible = msoTrue is not being applied to the first series (1st bubble). would someone be able to suggest why this might be happening?

thanks

Public Sub CreateMultiSeriesBubbleChart()
If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
MsgBox "Selection must have 4 columns and at least 2 rows"
Exit Sub
End If

Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=400, Top:=Selection.Top, Height:=250)
bubbleChart.Chart.ChartType = xlBubble
Dim r As Integer
For r = 2 To Selection.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
.Name = "=" & Selection.Cells(r, 1).Address(External:=True)
.XValues = Selection.Cells(r, 2).Address(External:=True)
.Values = Selection.Cells(r, 3).Address(External:=True)
.BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
.ApplyDataLabels AutoText:=True, LegendKey:=False, _
ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=True
.Format.Line.Visible = msoTrue
.Format.Line.Weight = 0.5
.Format.Fill.Solid
.Format.Fill.Transparency = 0.45

End With
end sub

 

[Bill Rockenbach]

You are starting at row 2 of your selection. Does row 1 have header info or is row one contain the first bubble series

Also your posted code appears to be missing a Next statement

 

[paul100]

yes it starts at row 2. 1st row is headings. the strange thing is that the transparency is applied to the first bubble but not the border.

yeah the next statement has been added before end.

Projects	Δ Time (Days)	Δ Cost ($M)	EAC $M
Project1	33	0.5	5
Project2	55	1.2	7
Project3	70	-0.5	12

<tbody>
</tbody>

 

[Bill Rockenbach]

Your Project3 cost is negative, therefore, I think you will see only two bubbles.

 

[paul100]

the bubbles are drawn perfectly. the issue is only with the formatting. ive posted the full macro below. if you could please try it with the data in the post above (select range including heading and execute macro) you will see the first series (bubble) does not have a border like the rest of the bubbles. other formatting like transparency is applied to all bubbles so im perplexed as to why the border is not being applied. im not a coder, rather a butcher, so I would greatly appreciate you assistance with this. its really bugging me.

cheers




Public Sub CreateMultiSeriesBubbleChart()
If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
MsgBox "Selection must have 4 columns and at least 2 rows"
Exit Sub
End If

Dim bubbleChart As ChartObject
Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=400, Top:=Selection.Top, Height:=250)
bubbleChart.Chart.ChartType = xlBubble
Dim r As Integer
For r = 2 To Selection.Rows.Count
With bubbleChart.Chart.SeriesCollection.NewSeries
.Name = "=" & Selection.Cells(r, 1).Address(External:=True)
.XValues = Selection.Cells(r, 2).Address(External:=True)
.Values = Selection.Cells(r, 3).Address(External:=True)
.BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
.ApplyDataLabels AutoText:=True, LegendKey:=False, _
ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=True
.Format.Line.Visible = msoTrue
.Format.Line.Weight = 0.5
.Format.Fill.Solid
.Format.Fill.Transparency = 0.45

End With


Next
bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)

bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)

bubbleChart.Chart.Axes(xlValue).MajorGridlines.Delete
bubbleChart.Chart.HasLegend = False
bubbleChart.Chart.ChartArea.Border.LineStyle = xlNone
bubbleChart.Chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8


End Sub

 

[Bill Rockenbach]

I'm using excel 2003 and get an error 1004 at this line of code

bubbleChart.

Chart.ChartType = xlBubble

 

[paul100]

im using the latest excel. that's a shame. would've been nice to get to the bottom of this.

thanks for looking Bill.

 

[Bill Rockenbach]

Paul,
I got some code working in excel 2003. The commented out statements are giving an error 1004 "object does not support this property or method." If you can tell me what this formatting is to be on each commented statement I'll take a look see.

Option Explicit
Public Sub CreateMultiSeriesBubbleChart()
    Dim r As Integer
    Dim rows As Long
    Dim bubbleChart As ChartObject
    rows = Selection.rows.Count
    If (Selection.Columns.Count <> 4 Or Selection.rows.Count < 3) Then
        MsgBox "Selection must have 4 columns and at least 2 rows"
        Exit Sub
    End If
    Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=400, Top:=Selection.Top, Height:=250)
    bubbleChart.Activate
    With ActiveChart
        'supply dummy minimum data range in order to convert to bubble chart
        .SetSourceData Source:=Sheets("Sheet1").Range("B1:D3"), PlotBy:= _
                       xlRows
        .ChartType = xlBubble
        'delete the dummy data range
        .SeriesCollection(1).Delete
        'construct the bubbles and do some formating
        For r = 2 To rows
            With .SeriesCollection.NewSeries
                .Name = "=Sheet1!R" & r & "C1"
                .XValues = "=Sheet1!R" & r & "C2"
                .Values = "=Sheet1!R" & r & "C3"
                .BubbleSizes = "=Sheet1!R" & r & "C4"
                .ApplyDataLabels AutoText:=True, LegendKey:=False, _
                                 ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _
                                 ShowPercentage:=False, ShowBubbleSize:=True
                '                .Format.Line.Visible = msoTrue
                '                .Format.Line.Weight = 0.5
                '                .Format.Fill.Solid
                '                .Format.Fill.Transparency = 0.45
            End With
        Next r
    End With
    'bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    'bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)
    'bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    'bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)
    bubbleChart.Chart.Axes(xlValue).MajorGridlines.Delete
    bubbleChart.Chart.HasLegend = False
    bubbleChart.Chart.ChartArea.Border.LineStyle = xlNone
    'bubbleChart.Chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
End Sub