Chart in a userform Option 1

[David Adamson]

Windows XP, Excel 2002.

Greetings all

I am trying to develop a graph on a userform that updates once data
selections have been made.
I have had a good look through previuos posts and through my copy of
Walkenbach's book.

I can do the bulk of what I want aprt from two things:

1. I want to be able to chose what I chart based on my slection from a
listbox.
2. X-axis to 100 (starting at 0, increment by 5 all the way to 100)

Any help would be appreciated.

David

--------
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
--
Sub Create_Charting_module()
Dim tempchart As Chart


'ChartExample.Data_To_Chart.ListIndex

'what need to do is based upon the users selection in the
'listbox then each series of data is then visible

For i = 1 To 4

'check =
If ChartExample.Data_To_Chart.Selected(i - 1) = True Then
MsgBox "Series " & i & " selected"
'want I want is this to select which data to chart
End If
Next i

'Get the data range to evaluate
If ChartExample.optionAllChart.value = True Then
a = 1
col = 20
End If
If ChartExample.OptionIndividualChart.value = True Then
a = ChartExample.YrIndividualChart
col = YrIndividual
End If
If ChartExample.OptionSeriesChart.value = True Then
a = ChartExample.YrstartChart
col = ChartExample.YrFinishChart
End If

If a = 0 Then
MsgBox "Please select the timeframe for the analysis. The minimum number is
1"
Exit Sub
End If

If col > 100 Then
MsgBox "The maximum number of years allowed is 100. Please adjust your data"
Exit Sub
End If

For c = 1 To col
With Worksheets("Spread Data")
Set Title_Names = Range("a6:a9")
Set Area_rng = Range(.Cells(6, 1 + a), .Cells(9, 2 + c))
End With
Set DataRange = Union(Title_Names, Area_rng)

Set tempchart = Charts.Add

With tempchart
.ChartType = xlLine
'need to defined where data is comming for for each series
.SetSourceData Source:=DataRange, PlotBy:=xlRows
.Location Where:=xlLocationAsObject, Name:="Spread Data"
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ChartExample.C_Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
ChartExample.X_Title
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
ChartExample.Y_Title
End With

'save out chart
Set CurrentChart = Sheets("Spread Data").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "temp.gif"
CurrentChart.Export Filename:=Fname, Filtername:="GIF"

'import it into the userform(ChartExample)
ChartExample.ChartImage.Picture = LoadPicture(Fname)
'do events gets it to work
DoEvents
'wait so user can see chart before changing
Sleep 250

'delete chart and start again

Worksheets("Spread Data").ChartObjects.Delete '
ActiveSheet.ChartObjects.Delete

Next c

End Sub

 

[Jon Peltier]

David -

1. Choose what you chart among what options?

2. Using a line chart precludes setting your X axis scale:

With tempchart
.ChartType = xlLine

Change xlLine to xlXYScatterLinesNoMarkers, then do this to set the scale:

With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 5
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[David Adamson]

Jon,

Thaks for the feedback on this and the other matter

David

news:[email protected]...

 

[Jon Peltier]

Glad to help, mate!

- Jon