modify code to suit 2013

[Keewi82]

Hi Everyone
i found this template online and was using it perfectly until i updated to 2013. Can anyone help me modify the code so it works with 2013.

Cheers

Option Explicit


Sub CallAB()

ActiveSheet.ChartObjects.Delete
ActiveSheet.Shapes.AddChart
AssignBubbleSource ActiveSheet.ChartObjects(1), ActiveSheet.Range("A1:D5")

End Sub


Private Sub AssignBubbleSource(chtBblChart As ChartObject, rngChartSource As Range, Optional blnHeader As Boolean = True)

Dim lngRow As Integer
Dim lngIndex As Byte
Dim wksSourceSheet As Worksheet

Const NameColumn As Integer = 0 'Change this value to change Name Column number
Const FirstColumn As Integer = 1 'Change this value to change column number of X Values
Const SecondColumn As Integer = 2 'Change this value to change column number of Y Values
Const ThirdColumn As Integer = 3 'Change this value to change column number of Z Values


Set wksSourceSheet = rngChartSource.Parent
With chtBblChart.Chart
.ChartType = xlBubble3DEffect
End With
For lngIndex = 1 To chtBblChart.Chart.SeriesCollection.Count
chtBblChart.Chart.SeriesCollection(1).Delete
Next lngIndex

lngIndex = 1

For lngRow = rngChartSource.Row + Abs(blnHeader) To rngChartSource.Row + rngChartSource.Rows.Count - 1
If wksSourceSheet.Cells(lngRow, rngChartSource.Column) = "" Then
GoTo AddNextItem
Else
With chtBblChart.Chart
.SeriesCollection.NewSeries
With .SeriesCollection(lngIndex)
.XValues = "='" & wksSourceSheet.Name & "'!R" & lngRow & "C" & (rngChartSource.Column + FirstColumn)
.Values = "='" & wksSourceSheet.Name & "'!R" & lngRow & "C" & (rngChartSource.Column + SecondColumn)
.BubbleSizes = "='" & wksSourceSheet.Name & "'!R" & lngRow & "C" & (rngChartSource.Column + ThirdColumn)
.Name = wksSourceSheet.Cells(lngRow, rngChartSource.Column + NameColumn).Value '"='" & strSourceShtName & "'!R" & lngRow & "C" & (rngChartSource.Column + NameColumn)
End With
End With
End If
lngIndex = lngIndex + 1
AddNextItem:
Next lngRow
With chtBblChart.Chart
.ChartType = xlBubble3DEffect
.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
.SetElement (msoElementPrimaryValueAxisTitleRotated)
.SetElement (msoElementDataLabelRight)
.Axes(1, 1).AxisTitle.Text = rngChartSource.Cells(1, rngChartSource.Column + FirstColumn).Value
.Axes(2, 1).AxisTitle.Text = rngChartSource.Cells(1, rngChartSource.Column + SecondColumn).Value
End If
End With
lngRow = Empty
lngIndex = Empty
Set wksSourceSheet = Nothing

End Sub

 

[Michael M]

What is it NOT doing correctly ??
If there is an error, which line gives the error ??
To do this use F8 to step through the code manually until you get an error line ( highlighted in yellow)

Can you please post using code tags in future....see my sig block

 

[Keewi82]

Thanks for the reply Michael

when i run the macro in 2013 an error 400 appears!

When i use the F8 function it appears to be this line ActiveSheet.ChartObjects.Delete

 

[Michael M]

Try skipping the error

Sub CallAB()
On Error Resume Next
ActiveSheet.ChartObjects.Delete
ActiveSheet.Shapes.AddChart
AssignBubbleSource ActiveSheet.ChartObjects(1), ActiveSheet.Range("A1:D5")

End Sub

 

[Keewi82]

Perfect!

Thanks