Code isnt working correctly & selects the incorrect cell on my worksheet

[ipbr21054]

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.

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

 

[ipbr21054]

I assumed the code would just do the same for each textbox.
I didn’t see why one minute it selects column A then on others B

Sorry about that.

 

[6StringJazzer]

You have overloaded your listbox. That is, you are putting different data in it depending on which textbox the user is filling in. Then when you do the search, you are looking for the selected value in the first column of the listbox. You are looking all over the entire worksheet.

I made a correction but this correction is good only for when the user types a VEHICLE and uses the list to search for the selected CUSTOMER. So you probably have to ignore my correction and make an overall design change.

You need to think about two things:

Based on what the user enters, which column in the listbox has the data you want to search for? Is it different depending on which textbox the user is entering?
Based on what you want to search for, where is it in the sheet?

Using the same listbox no matter what data you are searching for complicates matters because your code has to keep track of what you are searching for. Your original method of searching the entire worksheet is what is causing the problem that you came here to solve.

Then update the code to reflect that.

 

[ipbr21054]

Ok thanks for your time.

Will need to think about what im going to do.