ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,235
- Office Version
- 2007
- Platform
- Windows
Hi,
I am using the code shown below & wondering if you see a reason for which it isnt working correctly.
I have a userform where in a textbox i would type say JAZZ
In the list box all the records found on the worksheet for JAZZ are now shown.
The list would show customers name & say registration to now make it easier on the eye.
I look through the list & find the record i need to view.
So in this example lets say the populated list is as below.
JOM JONES
ROBER PLANT
PAUL WELLER
ELVIS
STING
So i select TOM JONES in the list, the userform closes BUT STING is selected on the worksheet NOT TOM JONES
I do the same again BUT ELVIS is then selected.
I do the same again BUT PAUL WELLER is then selected.
So it looks like no matter what i select the code selects the last name in the list then works its way up the list.
Obviously if i select JOM JONES i need TOM JONES to be selected.
Many Thanks.
I am using the code shown below & wondering if you see a reason for which it isnt working correctly.
I have a userform where in a textbox i would type say JAZZ
In the list box all the records found on the worksheet for JAZZ are now shown.
The list would show customers name & say registration to now make it easier on the eye.
I look through the list & find the record i need to view.
So in this example lets say the populated list is as below.
JOM JONES
ROBER PLANT
PAUL WELLER
ELVIS
STING
So i select TOM JONES in the list, the userform closes BUT STING is selected on the worksheet NOT TOM JONES
I do the same again BUT ELVIS is then selected.
I do the same again BUT PAUL WELLER is then selected.
So it looks like no matter what i select the code selects the last name in the list then works its way up the list.
Obviously if i select JOM JONES i need TOM JONES to be selected.
Many Thanks.
Rich (BB code):
Private Sub TextBoxVehicle_Change()
TextBoxVehicle = UCase(TextBoxVehicle)
Dim R As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DATABASE")
sh.Select
With ListBox1
.Clear
.ColumnCount = 3
.ColumnWidths = "210;260;80;"
If TextBoxVehicle.Value = "" Then Exit Sub
Set R = Range("D6", Range("D" & Rows.Count).End(xlUp))
Set f = R.Find(TextBoxVehicle.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(, -3).Value
.List(i, 2) = f.Offset(, -2).Value
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, -3).Value
.List(.ListCount - 1, 2) = f.Offset(, -2).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"
TextBoxVehicle.Value = ""
TextBoxVehicle.SetFocus
End If
End With
End Sub