CODEWITHSUNDEEP
vbaexcellistbox
Mario
Mario

Reputation: 177

Excel VBA: Searching for value in listbox based on value set in textbox

I am trying to write a code for a search button which searches a listbox based a specific input set in a textbox. The values searched are always numbers, and the listbox contains values from a single column. The code i wrote can be found below but i don't understand why it is not functional.

Legend:

Thanks for your help :)

Private Sub SearchButton_Click()
Dim SearchCriteria, i, n As Double
SearchCriteria = Me.SearchBox.Value
n = AvailableNumberList.ListCount
For i = 0 To n - 1
If SearchCriteria = i Then
AvailableNumberList.ListIndex = i
End If
Next i
End Sub

Upvotes: 2

Views: 17557

Answers (2)

savag
savag

Reputation: 11

Additional to @Siddharth Rout solution, this code allows to search in the ListBox even if the TextBox does not have the full word/number:

Private Sub SearchButton_Click()

Dim SearchCriteria, i, n As Double
SearchCriteria = Me.SearchBox.Value
n = AvailableNumberList.ListCount

For i = 0 To n - 1
If Left(AvailableNumberList.List(i),Len(SearchCriteria))=SearchCriteria Then
AvailableNumberList.ListIndex = i
Exit For
End If
Next i

End Sub

Thanks everyone for their code! =D

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149277

Is this what you are trying?

'If SearchCriteria = i Then
If AvailableNumberList.List(i) = SearchCriteria Then

Also use Exit For once a match is found :)

Upvotes: 2

Related Questions