Hello,
Why is it everytime i use the search textbox ,the listbox then comes up with multiple line with the same data?is there a way of preventing it?
Why is it everytime i use the search textbox ,the listbox then comes up with multiple line with the same data?is there a way of preventing it?
VBA Code:
Private Sub TextBox1_Change()
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Dim sh As Worksheet
Set sh = Sheets("Sheet1")
Dim i As Long
Dim x As Long
Dim p As Long
Me.ListBox1.Clear
Me.ListBox1.AddItem "Product ID"
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "ARTG ID"
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Product Description"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "Status"
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "Impact Start Date"
Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "Impact End Date"
Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "Usage End Date"
For i = 2 To sh.Range("F" & Rows.Count).End(xlUp).Row
For x = 1 To Len(sh.Cells(i, 6))
p = Me.TextBox1.TextLength
If LCase(Mid(sh.Cells(i, 6), x, p)) = Me.TextBox1 And Me.TextBox1 <> "" Then
With Me.ListBox1
.AddItem sh.Cells(i, 2)
'.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 3)
'.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 4)
.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, 5)
.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, 6)
.List(ListBox1.ListCount - 1, 3) = sh.Cells(i, 7)
.List(ListBox1.ListCount - 1, 4) = sh.Cells(i, 8)
.List(ListBox1.ListCount - 1, 5) = sh.Cells(i, 9)
.List(ListBox1.ListCount - 1, 6) = sh.Cells(i, 10)
'.List(ListBox1.ListCount - 1, 9) = sh.Cells(i, 11)
'.List(ListBox1.ListCount - 1, 10) = sh.Cells(i, 12)
'.List(ListBox1.ListCount - 1, 11) = sh.Cells(i, 13)
'.List(ListBox1.ListCount - 1, 12) = sh.Cells(i, 14)
'.List(ListBox1.ListCount - 1, 13) = sh.Cells(i, 15)
'.List(ListBox1.ListCount - 1, 14) = sh.Cells(i, 16)
'.List(ListBox1.ListCount - 1, 15) = sh.Cells(i, 17)
'.List(ListBox1.ListCount - 1, 16) = sh.Cells(i, 18)
End With
End If
Next x
Next i
End Sub