Chart on VBA Form

[dlx]

Hi,
Excel 2003
MS Office Chart 11.0
MS Office Spreadsheet 11.0

I need to create a chart on a VBA form from values calculated on the form.
There is 1 horizontal axis and 3 vertical axis with three lines on the chart -
see attachment

Is this possible with Chartspace or another control?
Thanks

[Kenneth Hobs]

Do the chart in a sheet. Update the chart values from the userform to cells on the sheet. Copy the chart object to a file and use the file in the Image object on the userform. e.g.

[VBA]Private Sub OptionButton1_Click()
ChartNum = 1
UpdateChart
End Sub
Private Sub OptionButton2_Click()
ChartNum = 2
UpdateChart
End Sub
Private Sub OptionButton3_Click()
ChartNum = 3
UpdateChart
End Sub
Private Sub OptionButton4_Click()
ChartNum = 4
UpdateChart
End Sub

Private Sub UserForm_Initialize()
ChartNum = 1
UpdateChart
End Sub
'Other methods, Stephen Bullen, http://www.oaltd.co.uk/Excel/Default.htm
'Method similar to http://www.j-walk.com/ss/excel/tips/tip66.htm
Private Sub UpdateChart()
Dim currentchart As Chart, Fname As String
Set currentchart = Sheets("Charts").ChartObjects(ChartNum).Chart
currentchart.Parent.Width = 400
currentchart.Parent.Height = 200

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
currentchart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub
[/VBA]

[dlx]

I am able to display the 3 data series on one chart, but each series has a different value range.
In the case below, I would like to show the 3 series on the y axis on the same chart.
km (0, 100, 200, ...),
liters (0, 10, 20, 30, ...),
days (0, 0.5, 1, 1.5, ...)
1. series1: y:distance, x:speed
2. series2: y:fuel, x:speed
3. series3: y:time, x:speed

[Kenneth Hobs]

For your scenario, show the two verticals and scale the third data for one of the other two verticals. You could overlay with a textbox to show the third scale.

If the method above will not suffice, see: http://peltiertech.com/Excel/Charts/TertiaryAxis.html

[dlx]

This is the Excel 2003 VBA code to add 2 additional Y-axis on the right side of the chart, but the 3 Y-axes have the same scale (see attached).
How can I associate a Y-axis with a different data series?

[vba]
Dim asCategory As Variant
Dim asValue1 As Variant
Dim asValue2 As Variant
Dim asValue3 As Variant

ReDim asCategory(100)
ReDim asValue1(100)
ReDim asValue2(100)
ReDim asValue3(100)

' fill asCategory, asValue1, asValue2, asValue3 arrays with data

With ChartSpace1

' Add a chart.
.Charts.Add
With .Charts(0)

' Create a line chart.
.Type = chChartTypeLineStackedMarkers

' Add three data series to the chart.
.SeriesCollection.Add
.SeriesCollection.Add
.SeriesCollection.Add

' Series 1
With .SeriesCollection(0)
.SetData chDimCategories, chDataLiteral, asCategory
.SetData chDimValues, chDataLiteral, asValue1
End With

' Series 2
With .SeriesCollection(1)
.SetData chDimCategories, chDataLiteral, asCategory
.SetData chDimValues, chDataLiteral, asValue2
End With

' Series 3
With .SeriesCollection(2)
.SetData chDimCategories, chDataLiteral, asCategory
.SetData chDimValues, chDataLiteral, asValue3
End With

' Set a variable to the Category (X) axis.
Set axCategoryAxis = ch.Axes(0)
' Set a variable to the Value (Y) axis.
Set axValueAxis = ch.Axes(1)
' The following two lines of code enable, and then
' set the title for the category axis.
axCategoryAxis.HasTitle = True
axCategoryAxis.Title.Caption = "Speed in Knots"

' Axis for series2
.Axes.Add ch.Scalings(chDimValues)
.Axes(2).Position = chAxisPositionRight

' Axis for series3
.Axes.Add ch.Scalings(chDimValues)
.Axes(3).Position = chAxisPositionRight
end with
end with
[/vba]

[dlx]

With the Office Web Components version 10 and version 11, you can have two value axis with different scales.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
286211 (support dot microsoft dot com slash kb slash 286211 )
How To Create a Combination Chart Using the Office XP Chart Component or the Office 2003 Chart Component