CODEWITHSUNDEEP
excelvba
Russ Urquhart
Russ Urquhart

Reputation: 329

How to use ListBox.ListIndex

I am going through a single select list box, to determine which series to select. The previous developer on this did the following:

For i = 0 To ListBox4.ListCount - 1
   If ListBox4.Selected(i) Then
      Series_Msg = Series_Msg & ListBox4.List(i) & vbNewLine
      ActiveChart.SeriesCollection(i + 1).Select

      'other commands

  Next i

In an attempt to clean up his code, i tried to do something like the following:

   If ListBox4.ListIndex <> -1 then

       ActiveChart.SeriesCollection(ListBox4.ListIndex + 1).Select

But i get an object required error. I tried declaring i as an object and assigning it the ListIndex value but that didn't work.

Can anyone suggest how i can do this without the loop? I can't believe that this loop is necessary.

Any help is greatly appreciated!

Russ

Upvotes: 1

Views: 51576

Answers (3)

David Zemens
David Zemens

Reputation: 53623

This code works fine for me, provided there is an ActiveChart. Add some handling to ensure that ActiveChart is not Nothing. Otherwise, you will get that error, Object Variable or With Block not set.

Sub UserForm_Activate()
    ListBox1.AddItem "First"
    ListBox1.AddItem "Second"
    ListBox1.AddItem "Third"
End Sub

Private Sub ListBox1_AfterUpdate()
Dim i As Integer

If ActiveChart Is Nothing Then 
    MsgBox "There is no ActiveChart!"
    Exit Sub
End If

i = ListBox1.ListIndex + 1

ActiveChart.SeriesCollection(i).Select
'Alternatively, you can ignore "i" and just use ActiveChart.SeriesCollection(ListBox1.ListIndex+1).Select

Unload UserForm1

End Sub

Upvotes: 0

Parrish Husband
Parrish Husband

Reputation: 3178

Make sure you've got the chart properly selected:

Worksheets("Sheet1").ChartObjects("Chart 1").Activate

If ListBox4.ListIndex <> -1 Then
    Series_Msg = Series_Msg & ListBox4.List(ListBox4.ListIndex, 0)
    ActiveChart.SeriesCollection(ListBox4.ListIndex + 1).Select
End If

Upvotes: 1

Felipe Costa Gualberto
Felipe Costa Gualberto

Reputation: 1107

Trye using: ActiveChart.SeriesCollection(ListBox4.List(ListBox4.ListIndex)).Select

Upvotes: 0

Related Questions