CODEWITHSUNDEEP
vbaexcellistbox
Amandine FAURILLOU
Amandine FAURILLOU

Reputation: 612

VBA Match value from listbox object to range

I am trying to check items in a listbox according to a range of cells (that matches)

This is what I've done so far.

Dim j As Integer
Dim lItem As Long
Dim rowx as Long

rowx = 12

j = 0
For lItem = 0 To Worksheets("Bordereau Prep").ListBoxPlot.ListCount

    If Worksheets("Bordereau Prep").ListBoxPlot.List(lItem) = Worksheets("Liste").Cells(rowx, 40 + j) Then
        Worksheets("Bordereau Prep").ListBoxPlot.Selected(lItem) = True
        j = j + 1
    End If

Next lItem

This does what I want, checking the items in the list that are in range_pr_el but it throws an error at :

If Worksheets("Bordereau Prep").ListBoxPlot.List(lItem) = Worksheets("Liste").Cells(rowx, 40 + j) Then

Telling me "Error 381 : Impossible to read List property. Index of the property table not valid". And I don't understand why, because it does enter the loop, and it does do what it's supposed to. What is missing to correct the error?

Thank you in advance

Upvotes: 1

Views: 749

Answers (1)

Z Engstrom
Z Engstrom

Reputation: 86

Try using

For lItem = 0 To Worksheets("Bordereau Prep").ListBoxPlot.ListCount - 1

When going through the for loop the last iteration will have lItem equal the number of list items. However, the list index starts at 0 so there should be a difference of 1.

For example if you had 1 list item the .ListCount method would give you 1 so the for loop will try to access list item with index 0 and list item with index 1. This would give you an error because the list box doesn't have 2 items.

`

Upvotes: 1

Related Questions