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

Question

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:

SearchButton: A Button which upon clicking is supposed to initiate the search
SearchBox: The textbox which will contain the search value
AvailableNumberList: The listbox which contains the data

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

Siddharth Rout · Accepted Answer · 2014-10-03 07:30:57Z

2

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 :)

answered Oct 3, 2014 at 7:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mario Over a year ago

(I'm kind of a noob in coding so I haven't used the "Exit for" previously) could you please point out where i should set that? Thanks :)

Siddharth Rout Over a year ago

after the line AvailableNumberList.ListIndex = i

savag · Accepted Answer · 2021-06-29 13:39:22Z

1

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

answered Jun 29, 2021 at 13:39

savag

111 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related