VBA creates charts (graph). How to draw another graph above it

Hi, I'm currently scriping VBA based on Excel data.
VBA code generates the Graph. I don't have problem to create column graph,
but, i need to add addtional line graph (Red line, please refer attached file) above it.

That data can come from Legend box or Data collection series.


this is code for create chart
=======================================
            Set objChart = .ChartObjects.Add( _
                Left:=myChtRange.Left, Top:=myChtRange.Top, _
                Width:=myChtRange.Width, Height:=myChtRange.Height)
           
                With objChart.Chart
               
               
               
                    .ChartArea.AutoScaleFont = False
                    .ChartType = xlColumnStacked
                    .SetSourceData Source:=myDataRange
                    .HasTitle = True
                    .ChartTitle.Characters.Text = "DATA"
                    .ChartTitle.Font.Bold = True
                    .ChartTitle.Font.Size = 12
                   
                        With .Axes(xlCategory, xlPrimary)
                            .HasTitle = True
                            With .AxisTitle
                                .Characters.Text = "Date"
                                .Font.Size = 10
                                .Font.Bold = True
                            End With
                        End With
                       
                        With .Axes(xlValue, xlPrimary)
                              .HasTitle = True
                              .MaximumScale = MaxCapacity
                              .MinimumScale = 0
                             
                            With .AxisTitle
                                .Characters.Text = "DATA - Y"
                                .Font.Size = 10
                                .Font.Bold = True
                            End With
                        End With
                       
                End With

===================================================================

How to add addtional red line graph based on legend.
Any comments would be appericiated.
VBA-Graph.png
ndoorsinteractiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Note the three statements I added at the bottom of the macro. These statements add a new series as a line chart on top of the column chart.
Sub Charter()
Dim objChart As ChartObject
Dim myDataRange As Range, myChtRange As Range
Dim MaxCapacity As Double
MaxCapacity = 10
With ActiveSheet
    Set myChtRange = .Range("E1:O20")
    Set myDataRange = .Range("A1:B5")
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
End With

With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlColumnStacked
    .SetSourceData Source:=myDataRange
    .HasTitle = True
    .ChartTitle.Characters.Text = "DATA"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 12
   
     With .Axes(xlCategory, xlPrimary)
         .HasTitle = True
         With .AxisTitle
             .Characters.Text = "Date"
             .Font.Size = 10
             .Font.Bold = True
         End With
     End With
    
     With .Axes(xlValue, xlPrimary)
           .HasTitle = True
           .MaximumScale = MaxCapacity
           .MinimumScale = 0
          
         With .AxisTitle
             .Characters.Text = "DATA - Y"
             .Font.Size = 10
             .Font.Bold = True
         End With
     End With
    
    .SeriesCollection.Add Source:=myDataRange.Columns(3)
    .FullSeriesCollection(2).ChartType = xlLine
    .FullSeriesCollection(2).AxisGroup = 1
End With
End Sub

Open in new window

0
ndoorsinteractiveAuthor Commented:
Thanks for your solution, - byundt
However,  by using my data, I've got a "Compile error: Method or data member not found"

could you please provide sample data that matches it with the script above..??

Thanks.
0
byundtMechanical EngineerCommented:
Here is my test workbook. Click on the button to run the macro.

I used Excel 2013. If my workbook isn't working at your end, please advise which version of Excel you use.

Brad
AutomateComboChartQ28225243.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

byundtMechanical EngineerCommented:
I tested the macro in Excel 2007 and got a compile error with .FullSeriesCollection(2). I found that getting rid of Full allowed the code to run. The following code is working in Excel 2007, 2010 and 2013.
Sub Charter()
Dim objChart As ChartObject
Dim myDataRange As Range, myChtRange As Range
Dim MaxCapacity As Double
MaxCapacity = 10
With ActiveSheet
    Set myChtRange = .Range("E1:O20")
    Set myDataRange = .Range("A1:B5")
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
End With

With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlColumnStacked
    .SetSourceData Source:=myDataRange
    .HasTitle = True
    .ChartTitle.Characters.Text = "DATA"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 12
   
     With .Axes(xlCategory, xlPrimary)
         .HasTitle = True
         With .AxisTitle
             .Characters.Text = "Date"
             .Font.Size = 10
             .Font.Bold = True
         End With
     End With
    
     With .Axes(xlValue, xlPrimary)
           .HasTitle = True
           .MaximumScale = MaxCapacity
           .MinimumScale = 0
          
         With .AxisTitle
             .Characters.Text = "DATA - Y"
             .Font.Size = 10
             .Font.Bold = True
         End With
     End With
    
    .SeriesCollection.Add Source:=myDataRange.Columns(3)
    .SeriesCollection(2).ChartType = xlLine
End With
End Sub

Open in new window

0
ndoorsinteractiveAuthor Commented:
========= please disregard this ===========

Oh, I'm using Excel 2007, Does it make a difference,..?
If so, what code i can use ..?

Thanks..
0
byundtMechanical EngineerCommented:
I'm using Excel 2007, Does it make a difference,..?
Excel VBA for charts changed enormously between Excel 2003 and 2007. Excel 2010 added macro recording for charts (something not possible in Excel 2007). Excel 2013 made some more changes to charting VBA along with a greatly improved charting user interface.

Bottom line: the version of Excel usually makes a difference in the answer to a forum question, either in "how to" instructions or in the details of the solution. Many people put the version in the question tags; if so, I'll test my suggestion in their version before posting.

FWIW, I recorded a macro in Excel 2013 (and later in Excel 2010) to convert the second series from a stacked column to a line chart. I don't do enough work with charting VBA to be able to write the code directly--it's much easier for me to edit the recorded bits and put them in useful form.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.