PDA

View Full Version : Solved: Search ListBox2 and Populate ListBox1 with Results



rrenis
03-01-2011, 01:25 PM
Hi, although I've posted this in the excel section the userform actually runs from outlook but as it seems a more 'general' query I posted it here, hope this is OK.

I'm trying to search the values of ListBox2 based upon text entered into a TextBox and write the results to ListBox1. I'd like to return all results that contain the text from the TextBox into ListBox1 so was thinking of using Instr to obtain matching data (by the way, both ListBoxes are both single select).

I've been tinkering around with the following code and getting nowhere fast so was hoping someone may have acheived something similar and would be willing to share some code or point me in the right direction. : pray2:

Here's the code I have which runs from a CommandButton...


mySearch = TextBox1.Value

For i = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(i) = True Then
If InStr(1, ListBox2.Value, mySearch, vbTextCompare) > 0 Then ListBox1.AddItem ListBox2.List(i)
End If
Next i


Thanks for looking :thumb

Cheers,
rrenis

Tinbendr
03-01-2011, 03:27 PM
I don't think you want to restrict by only the ones selected.

Private Sub CommandButton1_Click()
mySearch = TextBox1.Value

For i = ListBox2.ListCount - 1 To 0 Step -1
If InStr(1, ListBox2.List(i), mySearch, vbTextCompare) > 0 Then
ListBox1.AddItem ListBox2.List(i)
End If
Next i
End Sub

rrenis
03-01-2011, 03:42 PM
Doh! Thanks David! :bow:

Cheers,
rrenis