Excel combination of line and pie chart

Madara

New Member
Joined
Jan 6, 2015
Messages
13
Hi.

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

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??

VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
When want to add data to the graph you can use this maybe:
VBA Code:
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.
 
Upvote 0
Solution
When want to add data to the graph you can use this maybe:
VBA Code:
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top