ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,227
- Office Version
- 2007
- Platform
- Windows
Evening,
I have a userform with 3 textboxes & a listbox.
In the listbox are 5 columns
In the textbox named Customer if i type ZAC the results appear like this,
ZAC, HONDA, CBR, 2011, YES
So now in textbox named Make i type HONDA the results appear like this
HONDA, ZAC, CBR. 2011, YES
My goal is to have the listbox put the results in the same order each time as opposed having the fisrt column being what i typed in the search box.
Below is the code for the textbox named Make BUT please can you advise which part makes the searched value get placed in the first column.
Did i explain correctly for you ?
Thanks
I have a userform with 3 textboxes & a listbox.
In the listbox are 5 columns
In the textbox named Customer if i type ZAC the results appear like this,
ZAC, HONDA, CBR, 2011, YES
So now in textbox named Make i type HONDA the results appear like this
HONDA, ZAC, CBR. 2011, YES
My goal is to have the listbox put the results in the same order each time as opposed having the fisrt column being what i typed in the search box.
Below is the code for the textbox named Make BUT please can you advise which part makes the searched value get placed in the first column.
Did i explain correctly for you ?
Thanks
Rich (BB code):
Private Sub TextBoxMAKE_Change()
TextBoxMAKE = UCase(TextBoxMAKE)
Dim R As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DETAILS")
sh.Select
With ListBox1
.Clear
.ColumnCount = 5
.ColumnWidths = "150;180;150;100;80"
If TextBoxMAKE.Value = "" Then Exit Sub
Set R = Range("B3", Range("B" & Rows.Count).End(xlUp))
Set f = R.Find(TextBoxMAKE.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not f Is Nothing Then
cell = f.Address
Do
added = False
For i = 0 To .ListCount - 1
Select Case StrComp(.List(i), f.Value, vbTextCompare)
Case 0, 1
.AddItem f.Value, i
.List(i, 1) = f.Offset(, -1).Value
.List(i, 2) = f.Offset(, 1).Value
.List(i, 3) = f.Offset(, 2).Value
.List(i, 4) = f.Offset(, 5).Value
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, -1).Value
.List(.ListCount - 1, 2) = f.Offset(, 1).Value
.List(.ListCount - 1, 3) = f.Offset(, 2).Value
.List(.ListCount - 1, 4) = f.Offset(, 5).Value
End If
Set f = R.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
TextBoxSearch = UCase(TextBoxSearch)
.TopIndex = 0
Else
MsgBox "NO ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET ITEM SEARCH"
TextBoxMAKE.Value = ""
TextBoxMAKE.SetFocus
End If
End With
End Sub