ndoorsinteractive
asked on
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.Tex
.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
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.Tex
.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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
========= please disregard this ===========
Oh, I'm using Excel 2007, Does it make a difference,..?
If so, what code i can use ..?
Thanks..
Oh, I'm using Excel 2007, Does it make a difference,..?
If so, what code i can use ..?
Thanks..
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.
ASKER
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.