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]

Hi,

What kind of control? MS Form or Control Toolbox?

How is the list range defined to the listbox? AddItem or Range?

Skip,

_[red][/red]

 

[Groubas]

Hi

Im not quite sure what you mean (Im new at this). Its a user form - a multipage to be specific from the xl visual basic editor.

the part when I copy the values from the listbox to a worksheet is already done in the program.

The values were added by additem.

I want the chart to be plotted (=updated) as i press a commandbutton in the userform.

I dont know if something of this answers your question(?)

 

[SkipVought]

In your chart...

right click and select the Source Data tab

Are there sheet ranges defined for each series?

Skip,

_[red][/red]

 

[Groubas]

I have the chart and program at work so I cant check that right now, but im quite sure that the ranges are defined... is that a problem?

 

[SkipVought]

Sorry, I should have noticed that your code example answered my question.

But the primary question is, how does a selection in the list box relate to the range being plotted?

Skip,

_[red][/red]

 

[Groubas]

ok, the user adds values to a listbox in the userform where the values are sorted like i want them, then by pressing a "plot"-button the values are copied to a worksheet and from there they are used to plot the chart.

this is why I set chartEnd=listcount

 

[SkipVought]

So you have 3 series.

how do the values from the listbox get into 3 columns (series)? That is, what's the LOGIC?

Skip,

_[red][/red]

 

[Groubas]

Im not sure what that has to do with this problem?

could you explain?

 

[SkipVought]

Explain the process in excruciating detail.

For instance, lets say that the user enters 3 data point values, 2,5,9.

What happens to these values?

How do they relate to your 3 columns (series)?

Skip,

_[red][/red]

 

[Groubas]

its a xy-scatter chart (as you can se by the code). the user only enters the first two columns (x and y) the following columns are counted by the program.

The program is more than 1500 rows so It would take too long to explain it all. The basic thing is that the values are now in the worksheet and I want them to be plotted in the chart.

Now regarding how I want to present the values thats all taken care of since I once plotted the values "manually" and then set the chart to look like i want it to. Now I want to set the range (from 1 to "chartEnd") for the different values depending on how many rows with the initiating x and y values the user wants to add to the program.

 

[SkipVought]

    With Worksheets("Blad1")
[b]
      chartEnd = .Cells(.cells.rows.count, 1).end(xlup).row
[/b]
    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

Skip,

_[red][/red]

 

[Groubas]

isnt that the same code that i posted? The listbox is still populated after i copy all the values to the worksheet so

chartEnd = Me.lstCoords.ListCount

should generate the same value as

chartEnd = .Cells(.cells.rows.count, 1).end(xlup).row

isn't that right?

 

[Groubas]

... but ill try your code as soon as i get to work tomorrow.


anyway, thank you!

 

[SkipVought]

"isnt that the same code that i posted? "

NO!

I have no idea what's in your listbox.

Did you try it?

Skip,

_[red][/red]

 

[Groubas]

I have no idea what's in your listbox."

the listbox contains the same values as the fist two columns in the worksheet

and no I havent tried it yet

 

[SkipVought]

then what is not working?

Skip,

_[red][/red]

 

[Groubas]

Skip>

The code that you posted didnt work at all

and regarding your last question - If I knew I wouldn't post this thread, would I?

 

[SkipVought]

Common, bud. This is like going in to the doctor and saying, "fix me!" When the doctor says, "what hurts?" you say, "If I knew I wouldn't be here, would I?"

You've got to be a bit more forthcoming. I'm not a mindreader.

Skip,

_[red][/red]

 

[Groubas]

.... right, but if I showed the doctor all my values and told him about my problem them Im sure he would know whats wrong... that is if he would be a real doctor


anyway I showed the code to others and they knew right away whats wrong with it so the problems solved.