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 Whe=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

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 wrote:

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 Whe=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

Glad to help, mate!

- Jon

David Adamson wrote:

Jon,

Thaks for the feedback on this and the other matter

David

"Jon Peltier" wrote in message
...