Search Listbox and Click it Find

[russkie]

Hello,

Is there anyway to search a populated listbox for something and have the list box "select" it as if the user clicked on it?

Example:
listbox containt these with "-" seperating columns:
111 - hello
222 - no
333 - whoopy

i prompt the user what to search for in the listbox. He enters 333 and hits search. Now i made the "listbox_click()" that it causes a textbox to show the second column of the item clicked on in the listbox. so when the user hits search, i want that it will search for "333" in the listbox and make it act is if the user clicked on it...

thanks for any help.

[tony_813]

Hello,
If you are working from a userform...
The code may look like the following:

Private Sub cmdSearch_Click()
Dim i As Long
With ListBox1
For i = 0 To .ListCount
If .Column(0, i) = TextBox1.Value Then
'Selects the matching entry
.ListIndex = i
'Any Addtional Code..
Exit For
End If
Next i
End With
End Sub

tony_813

[tony_813]

Also if your are working with a ListBox and a TextBox from a worksheet..
The code may look like the following:


Dim i As Long
Dim lstBox As OLEObject, txtBox As OLEObject
Set lstBox = Worksheets("Sheet1").OLEObjects("ListBox1")
Set txtBox = Worksheets("Sheet1").OLEObjects("TextBox1")

With lstBox.Object
For i = 0 To .ListCount
If .Column(0, i) = txtBox.Object.Value Then
'Selects the matching entry
.ListIndex = i
'Any Addtional Code..
Exit For
End If
Next i
End With

Set lstBox = Nothing: Set txtBox = Nothing

[russkie]

Thats good but something will be wrong with it. Say the user doesnt type in EXACTLY the right words, if lets say on of the items were "hello darling" and the user types in "hello", it wouldnt find "hello darling" in the listbox...

[tony_813]

Hello,
One Option is to throw an error message if not found:
Dim i As Long
With ListBox1
For i = 0 To .ListCount - 1
If .Column(0, i) = TextBox1.Value Then
'Selects the matching entry
.ListIndex = i
'Any Addtional Code..
Exit Sub
End If
Next i
MsgBox "Your Entry Was Not Found", vbCritical, "Entry Not Found"
End With

Or Second.. Is to build the search with a wild card..at the end..

Dim i As Long
On Error GoTo ErrFound
With ListBox1
For i = 0 To .ListCount - 1
If WorksheetFunction.Search(TextBox1.Value & "*", _
.Column(0, i), 1) > 0 Then
'Selects the matching entry
.ListIndex = i
'Any Addtional Code..
Exit Sub
End If
Next i
ErrFound:
MsgBox "Your Entry Was Not Found", vbCritical, "Entry Not Found"
End With

[russkie]

i was working on it a while last night i think i found a pretty cool way to do this. I got all the information from the listbox from columns (A:A) or its possible to get the list box to past the informaiton there. Then i kept a temp "placement" number in : "Worksheets("Programming").Range("A14")" so this works pretty cool:




Private Sub FindProductName_Click()
Dim ProductName As Range
Dim Rmmbr As Range

Restart:
If (Worksheets("Programming").Range("A14") > 0) Then GoTo FindMore

With Worksheets("Sheet1").Range("A:A")
Set ProductName = .Find(Fproductname, lookat:=xlPart)
If Not ProductName Is Nothing Then
i = ProductName.Row
j = i - 3
End If
End With

InventoryForm.ProductBox.ListIndex = j
FindProductName.Caption = "Find Next"
Worksheets("Programming").Range("A14").Value = Val(i)
Exit Sub


FindMore:
i = Worksheets("Programming").Range("A14").Value
Set Rmmbr = Range("A" & i)

With Worksheets("Sheet1").Range("A:A")
Set ProductName = .Find(Fproductname, lookat:=xlPart, after:=Rmmbr)
If Not ProductName Is Nothing Then
Do While ProductName.Row < i
Set ProductName = .FindNext(ProductName)
If Not ProductName Is Nothing Then
GoTo SelectIt
End If
Loop
End If
End With

If ProductName Is Nothing Then GoTo Restart

SelectIt:
i = ProductName.Row
j = i - 3
InventoryForm.ProductBox.ListIndex = j
Worksheets("Programming").Range("A14").Value = Val(i)

End Sub

Private Sub Fproductname_Change()
FindProductName.Caption = "Find It"
Worksheets("Programming").Range("A14").Value = 0
End Sub


jost posting it maybe itll help someone or maybe someone has a more efficient way of tdoing this.. all in all thanks for the help tony.