Edit for existing working code

[ipbr21054]

Morning.

The code below works but looking for an edit please.
I type AB02 in the search box & the listbox returns the results from column E
I wish to now type AB02 but in the listbox be able to see not only like before the results from column E BUT also column F,G,H

So it would look like this.
I type AB02 and the listbox would show example below
AB02
AAB02
AAAB02

But the new edit would return this,

AB02 BLUE 55 ABC123
AAB02 GREEN 88 DEF 456
AAAB02 RED 11 GHI 789

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("MC VIN")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("E11", Range("E" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.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.Row
              added = True
              Exit For
          End Select
        Next
           If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBox1 = UCase(TextBox1)
      Else
      MsgBox "NO VIN MODEL WAS FOUND USING THAT INFORMATION", vbCritical, "VIN MODEL SEARCH MESSAGE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Hi,

Here is a better explantion of what i would like to happen.

As you can see in my example i have searched for AB02
The results are shown in the lisbox & the AB02 options are shown.

BUT I REQUIRE THIS PLEASE

My required results with the new edit should now show AB02 & also the next 3 column results so E,F,G,H
Looking at the database below the userform should help.

I would like to see the results like this in the listbox.

AB02 Honda z50 G Monkey Z-D 1979-1983 " NOT JUST AB02 LIKE BEFORE"

 

[ipbr21054]

Any advice please.

 

[DanteAmor]

Try this
The row number is in the 5th column of the listbox.
I guess the header of your data is in row 10.

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean, sh As Worksheet
  Set sh = Sheets("MC VIN")
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = sh.Range("[B][COLOR=#008000]E10[/COLOR][/B]", sh.Range("E" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      Cell = f.Address
      Do
        .AddItem f.Value ', i
        .List(.ListCount - 1, 1) = f.Offset(, 1).Value
        .List(.ListCount - 1, 2) = f.Offset(, 2).Value
        .List(.ListCount - 1, 3) = f.Offset(, 3).Value
[B][COLOR=#0000ff]        .List(.ListCount - 1, 4) = f.Row[/COLOR][/B]
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBox1 = UCase(TextBox1)
    Else
      MsgBox "NO VIN MODEL WAS FOUND USING THAT INFORMATION", vbCritical, "VIN MODEL SEARCH MESSAGE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Hi,

With that code i then see the following.
So if i search AB02 the results shown in the listbox doesnt match the database.

In the listbox i should see,
VIN MODEL PREFIX
HONDA MODEL
YEAR CODE
YEAR

 

[DanteAmor]

The problem is not my code.
It is this code:

.ColumnWidths = "[COLOR=#0000ff]100;0[/COLOR]"

Change it to this:

.ColumnWidths = "100;100;100;100;0"

 

[ipbr21054]

You will need to check the code below but i see in the listbox the same as in worksheet

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean, sh As Worksheet
  Set sh = Sheets("MC VIN")
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = sh.Range("E10", sh.Range("E" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      Cell = f.Address
      Do
        .AddItem f.Value ', i
        .List(.ListCount - 1, 1) = f.Offset(, 0).Value
        .List(.ListCount - 1, 2) = f.Offset(, 1).Value
        .List(.ListCount - 1, 3) = f.Offset(, 2).Value
        .List(.ListCount - 1, 4) = f.Offset(, 3).Value
        .List(.ListCount - 1, 5) = f.Row
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBox1 = UCase(TextBox1)
    Else
      MsgBox "NO VIN MODEL WAS FOUND USING THAT INFORMATION", vbCritical, "VIN MODEL SEARCH MESSAGE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

BUT

Clicking on an item i am taken to a cell like EAB

 

[DanteAmor]

But you modified my code, just use the code from post #4
and change this line:

[COLOR=#333333].ColumnWidths = "100;100;100;100;0"[/COLOR]

 

[ipbr21054]

Hi,
I have put in use the code below as advised.

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean, sh As Worksheet
  Set sh = Sheets("MC VIN")
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "80;270;100;100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = sh.Range("E10", sh.Range("E" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, lookat:=xlPart)
    If Not f Is Nothing Then
      Cell = f.Address
      Do
        .AddItem f.Value ', i
        .List(.ListCount - 1, 1) = f.Offset(, 1).Value
        .List(.ListCount - 1, 2) = f.Offset(, 2).Value
        .List(.ListCount - 1, 3) = f.Offset(, 3).Value
        .List(.ListCount - 1, 4) = f.Row
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBox1 = UCase(TextBox1)
    Else
      MsgBox "NO VIN MODEL WAS FOUND USING THAT INFORMATION", vbCritical, "VIN MODEL SEARCH MESSAGE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub


I now click on a item in the listbox but see a RTE Method Range Of Object Global Failed.
I debug andd se the following in yellow.

[CODE]
Private Sub ListBox1_Click()
[COLOR=#ff0000]  Range("E" & ListBox1.List(ListBox1.ListIndex, 1)).Select[/COLOR]
  Unload HondaMcVinSearch
End Sub

[/CODE]

 

[ipbr21054]

I now click on a item in the listbox but see a RTE Method Range Of Object Global Failed.
I debug andd se the following in yellow.



Code: [View]


Private Sub ListBox1_Click()
Range("E" & ListBox1.List(ListBox1.ListIndex, 1)).Select
Unload HondaMcVinSearch
End Sub