Excel combination of line and pie chart

[Madara]

Hi.

I'm trying to work out this chart tutorial made available here.

Combination Line & Pie Chart

Way back in February of 2013, I published a how to guide on how to create a world map pie chart using vba and Excel. Although I wrote the how to, the original work was of course done by Andy Pope. …

alesandrab.wordpress.com

You can download my working file from this link.
https://file.io/3ByKoHsnXLDT

I just couldn't figure out the reason why this does not work. Maybe the VBA code is too old??

Sub PieMarkers()

Dim chtMarker As Chart
Dim chtMain As Chart
Dim intPoint As Integer
Dim rngRow As Range
Dim lngPointIndex As Long

Application.ScreenUpdating = False
Set chtMarker = ActiveSheet.ChartObjects(“chtMarker”).Chart
Set chtMain = ActiveSheet.ChartObjects(“chtMain”).Chart

Set chtMain = ActiveSheet.ChartObjects(“chtMain”).Chart
Set rngRow = Range(ThisWorkbook.Names(“PieChartValues”).RefersTo)

For Each rngRow In Range(“PieChartValues”).Rows

chtMarker.SeriesCollection(1).Values = rngRow
chtMarker.Parent.CopyPicture xlScreen, xlPicture
lngPointIndex = lngPointIndex + 1
chtMain.SeriesCollection(1).Points(lngPointIndex).Paste

Next

lngPointIndex = 0

Application.ScreenUpdating = True

End Sub

When i run it i get "Run-time error '1004': Application-defined or object-defined error

 

[mart37]

It works with only the data you used at the first time. The exsisting data can you change but you can't use new rows.

 

[mart37]

When want to add data to the graph you can use this maybe:

Sub PieMarkers()
    Dim chtMarker As Chart
    Dim chtMain As Chart
    Dim rngRow As Range
    Dim rngLineChart As Range
    Dim lngPointIndex As Long
    Application.ScreenUpdating = False
    Set chtMarker = ActiveSheet.ChartObjects("chtMarker").Chart
    Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart
    Set rngRow = Range(ThisWorkbook.Names("PieChartValues").RefersTo)
    Set rngLineChart = Range(ThisWorkbook.Names("LineChartValues").RefersTo)
    chtMain.SetSourceData Source:=rngLineChart
    For Each rngRow In Range("PieChartValues").Rows
        chtMarker.SeriesCollection(1).Values = rngRow
        chtMarker.Parent.CopyPicture xlScreen, xlPicture
        lngPointIndex = lngPointIndex + 1
        chtMain.SeriesCollection(1).Points(lngPointIndex).Paste
    Next
    lngPointIndex = 0
    Application.ScreenUpdating = True
End Sub

I have add a named range: LineChartValues
This name refers to the data for the line chart.
When you change both name ranges the chart shall update the new rows.

 

[Madara]

When want to add data to the graph you can use this maybe:

Sub PieMarkers()
    Dim chtMarker As Chart
    Dim chtMain As Chart
    Dim rngRow As Range
    Dim rngLineChart As Range
    Dim lngPointIndex As Long
    Application.ScreenUpdating = False
    Set chtMarker = ActiveSheet.ChartObjects("chtMarker").Chart
    Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart
    Set rngRow = Range(ThisWorkbook.Names("PieChartValues").RefersTo)
    Set rngLineChart = Range(ThisWorkbook.Names("LineChartValues").RefersTo)
    chtMain.SetSourceData Source:=rngLineChart
    For Each rngRow In Range("PieChartValues").Rows
        chtMarker.SeriesCollection(1).Values = rngRow
        chtMarker.Parent.CopyPicture xlScreen, xlPicture
        lngPointIndex = lngPointIndex + 1
        chtMain.SeriesCollection(1).Points(lngPointIndex).Paste
    Next
    lngPointIndex = 0
    Application.ScreenUpdating = True
End Sub

I have add a named range: LineChartValues
This name refers to the data for the line chart.
When you change both name ranges the chart shall update the new rows.

Thank you very much. It works