Number of chart values depending on number of populated rows in listbx

[Groubas]

Hello

Ive tried to figure this out but I cant get this to work.

The problem is that I want to plot a chart but the number of values varies depending on how many listbox rows that the user has populated.

This is my code so far, but it doesnt work:

Sub plotChart()
Dim chartEnd As Long

chartEnd = Me.lstCoords.ListCount


With Worksheets("Blad1")
ChartObjects("Diagram20").SeriesCollection(1).XValues = .Range(.Cells(1, 1), .Cells(chartEnd, 1))
ChartObjects("Diagram20").SeriesCollection(1).Values = .Range(.Cells(1, 2), .Cells(chartEnd, 2))
ChartObjects("Diagram20").SeriesCollection(2).XValues = .Range(.Cells(1, 4), .Cells(chartEnd, 4))
ChartObjects("Diagram20").SeriesCollection(2).Values = .Range(.Cells(1, 5), .Cells(chartEnd, 5))
If Not txtXA.Value = "" Then
ChartObjects("Diagram20").SeriesCollection(3).XValues = .Range(.Cells(1, 7), .Cells(chartEnd, 7))
ChartObjects("Diagram20").SeriesCollection(3).Values = .Range(.Cells(1, 8), .Cells(chartEnd, 8))
End If
End With

End Sub


The chart (a scatterchart if that would make things different) has been set once, i just want to update the values (and of cource the number of values)

 

[SkipVought]

Well I am pleased that you found a solution.

It would help both me and other Tek-Tipers who read this thread, if you would explain the solution that worked.

Skip,

_[red][/red]

 

[Groubas]

Heres the code that works:

Sub plotChart()
Dim chartEnd As Long

chartEnd = Me.lstCoords.ListCount


With ThisWorkbook.Sheets("TempData")
.ChartObjects("Diagram 20").Chart.SeriesCollection(1).XValues = .Range(.Cells(1, 1), .Cells(chartEnd, 1))
.ChartObjects("Diagram 20").Chart.SeriesCollection(1).Values = .Range(.Cells(1, 2), .Cells(chartEnd, 2))
.ChartObjects("Diagram 20").Chart.SeriesCollection(2).XValues = .Range(.Cells(1, 4), .Cells(chartEnd, 4))
.ChartObjects("Diagram 20").Chart.SeriesCollection(2).Values = .Range(.Cells(1, 5), .Cells(chartEnd, 5))
End With

UpdateChart

End Sub

I had to use a . (dot) after with (Which I didnt know) and I had to add .Chart after ChartObjects