Multiple Chart objects on one sheet


Active Member
Jun 25, 2014

I would like to program two charts on a single sheet. The charts will be deleted and recreated when a button is pressed. (I need two charts to be visible at the same time).

My code was working fine with a single chart object but when I added an additional chart: the chart type, title and other attributes are not showing up on the second chart.

The two charts should have the same structure but reference a different column on the sheet. I looked around but couldn’t find anything that would help. My code is really long so I’m posting partial code. I’ve tried so many things and nothing is working. Please suggest something.


Function GraphMFI (Arr() As Variant, Arr2() As Variant, ChartName As String, ChartName2 As String)

Dim i As Long, l As Long
Dim rng As Range, aCell As Range
Dim MyArY() As Variant, MyArX() As Variant
Dim LastRow As Long, iVal As Long
Dim Count As Long, SumArr As Long, AvgC As Long

Application.EnableEvents = False

'Code that calculates x and y values not shown
On Error Resume Next

'~~~~~~~~~chart code begins

Call DeleteallCharts 'delete all existing charts from active sheet
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~add both charts~~~~~~
Set objChart = ActiveSheet.ChartObjects.Add _
(Left:=410, Width:=500, Top:=15, Height:=250)
objChart.Chart.ChartType = xlXYScatterLines

Set objChart2 = ActiveSheet.ChartObjects.Add _
(Left:=410, Width:=500, Top:=300, Height:=250)
objChart.Chart.ChartType = xlXYScatterLines
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~add both charts~~~~~~

Dim objChartSeriesColl As SeriesCollection
Dim objChartSeriesColl2 As SeriesCollection

Set objChartSeriesColl = objChart.Chart.SeriesCollection
Set objChartSeriesColl2 = objChart2.Chart.SeriesCollection
'delete all chart series

'~~~~~~~~~~~first chart
With objChartSeriesColl.NewSeries '~~~raw data

.Name = "Inner Run Variability"
.Values = Arr
.XValues = rng
.MarkerSize = 10
    'code not shown
End With

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~First Chart
With objChartSeriesColl.NewSeries '~~~average series one
Dim nPts As Long
.Name = "Mean"
.Values = AvgArr '~~~~average of Negative control
.XValues = rng '~~~dates
'.AxisGroup = xlSecondary
.ChartType = xlXYScatterLinesNoMarkers

 'With mySrs
        nPts = .Points.Count
        .Points(nPts).ApplyDataLabels _
            Type:=xlDataLabelsShowValue, _
            AutoText:=True, LegendKey:=False
        .Points(nPts).DataLabel.Text = .Name
        .Points(nPts).ApplyDataLabels Type:=xlDataLabelsShowValue, _
                    AutoText:=True, LegendKey:=False
With .DataLabels
            .AutoScaleFont = False
            .Font.Size = 10
            .Font.ColorIndex = 3
            .Position = xlLabelPositionAbove
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .Orientation = xlHorizontal
        End With

End With

With objChartSeriesColl.NewSeries '~~plus two stdev series two
.Name = "plus 2 stdev"
.Values = TwoPlusSdtDevArr
.XValues = rng '~~~dates
End With

With objChartSeriesColl.NewSeries 'minus three stdev series three
.Name = "minus 2 stdev"
.Values = TwiceStdDevArr
.XValues = rng
.ChartType = xlXYScatterLinesNoMarkers
End With

'~~~~~~~~~~~Second chart
With objChartSeriesColl2.NewSeries '~~~raw data
.Name = "Inner Run Variability"
.Values = Arr2
.XValues = rng
.MarkerSize = 10
End With

'~~~~~adding series to the second chart

With objChartSeriesColl2.NewSeries '~~~average
Dim nPts2 As Long
.Name = "Mean"
.Values = AvgArr
.XValues = rng '~~~dates
.ChartType = xlXYScatterLinesNoMarkers

End With

'....more series not shown here

With objChart

            .Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy" 'changes Xaxis text format
            .Axes(xlValue).TickLabels.NumberFormat = "General" 'changes Yaxis Text Format
            .SetElement (msoElementChartTitleAboveChart) 'adds chart title above chart
            .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) 'adds Xaxis title
            .SetElement (msoElementPrimaryValueAxisTitleRotated) 'adds rotated Yaxis Title

            .ChartTitle.Text = ChartName  'adds chart title above chart
            .SetElement (msoElementLegendNone)

            '~~~~~~~~~~~~set plot area
            With .PlotArea
                    .Width = .Width / 2
                    .Height = .Height / 2
                    .Left = 16
                    .Top = 16
                    .Width = 450
                End With
            With .Axes(xlCategory, xlPrimary)
                .AxisTitle.Text = "Run Dates" 'renames Xaxis title to "X Title"
                .AxisTitle.Font.Bold = True
          End With
            With .Axes(xlValue, xlPrimary)
                .AxisTitle.Text = "Sample Dates" 'renames Xaxis title to "X Title"
                .AxisTitle.Text = "MFI Values" 'renames Yaxis title to "Y Title"
            End With
            .Axes(xlCategory).MinimumScale = ChartMin '~~adds min

            .Axes(xlCategory).MaximumScale = ChartMax '~~ adds max
            .Parent.Placement = xlFreeFloating

            With .ChartArea.Format.Line
                .Visible = msoCTrue
                .Style = msoLineSingle
                .Weight = 1
                .ForeColor.RGB = RGB(255, 255, 255)

            End With

        End With
        'more code
End With

''''~~~~~~~~~~~~~Second Chart begins here

With objChart2
'code almost the same as 'with objChart'

  Application.EnableEvents = True
End With
End Function

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college


Aug 16, 2002
Office Version
  1. 365
  2. 2019
  1. Windows
  2. MacOS
get rid of the on error resume next - it may help you pinpoint where the problem is

your setting of the chart type for the second chart is referencing the wrong objChart

Step through your code (F8) as you watch it update your chart - you might see what's going on more clearly.

I don't know if it's your pasting or how your actual code is indented, but the indention here makes it very difficult to read your code.

Finally - I would comment out all the lines pertaining to the 1st chart and see if the 2nd chart can be created at all.

Forum statistics

Latest member

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
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 "".
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