Search box code doesnt select correct cell on worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,202
Morning,


I have taken this code from another worksheet & made the edits for it to work on my new sheet.
I type in the search box say AB & all the results with AB are shown in the listbox.
I select the item i require.

My problem is that the item i selected isnt selected on my worksheet in column E
In fact what is selected is the side bar.

Can you advise what i missed in the code so the cell in column E should be selected.
Thanks

Code:
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,202
Just an update.
i found this code which now takes me to column E
The issue now is finding why it doesnt select the correct item of which i had selected from the list box

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,202
Hi,
I have found the issue but need some advice if i may to fix it.

If in my database there is only 1 occurance of say AZ1 then when i search for AZ1 i select it in the listbox & i am taken to it no problem.

However if there are say 5 occurancies of AZ1 then all AZ1 are shown in the list box but selecting one of them doesnt take me to the correct item selected.
As a test i did this.
AZ1 1
AZ1 2
AZ1 3
AZ1 4
AZ1 5

Now when i search AZ1 i see all the AZ1-5 options in the listbox.
Selecting one then i am taken to it no problem.

So can you advise an edit so with the original results
AZ1
AZ1
AZ1
AZ1
AZ1
No matter which i select i MUST be taken to the first AZ1 in the worksheet.

This will then be ok for me

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,739
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top