Userform selection doesnt select correct item on nworksheet

[ipbr21054]

Hi,
I have a userform which has various textboxes that search certain columns on my worksheet.
One called TextBoxVehicle searches column D
I type PEUGEOT 308 & i see 3 results appear in the Listbox.
The 3 results are as follows,
VICTORIA HUDSON
HAYDEN WILLIAMS
ANDY WYATT

So i select VICTORIA HUDSON & the cell in column D is selected, which is good.
BUT
I then open the userform again & type PEUGEOT 308 but this time i select HAYDEN WILLIAMS & ANDY WYATT is selected ?
I do the same again & select HAYDEN WILLIAMS & ANDY WYATT is selected ?

I dont see why,
I have supplied the code for TextBoxVehicle & ListBox1

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

Private Sub ListBox1_Click()
        
 Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
  Unload DatabaseSearchForm
End Sub

 

[ipbr21054]

Some info for you.
If i type PEUGEOT i see the following in the listbox

PEUGEOT 206 CUSTOMER A
PEUGEOT 207 CUSTOMER B
PEUGEOT 308 CUSTOMER C
PEUGEOT 308 CUSTOMER D
PEUGEOT 308 CUSTOMER E
PEUGEOT BIPPER CUSTOMER
It seems that if i select the vehicle where there is only 1 type example PEUGEOT 206 then i am taken to CUSTOMER A of which is correct
Same goes for PEUGEOT 207 & BIPPER

The issue seems to be when there are more than 1 of the same like PEUGEOT 308 then the selection becomes random

 

[ipbr21054]

I’ve been looking & looking but still can’t get this to work.

But from memory I’m missing something.

Not sure of the exact code but something like the below needs to be added so when I click the listbox entry I’m taken to the row that customer details are on.

.Cell select

Anybody she’s any light on this please.
My code in use is above but can’t for the life of me remember what the code is that I’m missing & where I need to put it.

Many thanks.