Hi
I have created a sheet that searches a price list from the description colulmn and also company column of the price list then puts into the list box
I works fine but typing in the search box Textbox1 is very slow/laggy
I can't seem to speed it up
the code is
I have created a sheet that searches a price list from the description colulmn and also company column of the price list then puts into the list box
I works fine but typing in the search box Textbox1 is very slow/laggy
I can't seem to speed it up
the code is
VBA Code:
Private Sub TextBox1_Change()
Dim I As Long
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Me.ListBox1.Clear
Me.ListBox1.AddItem "Item"
Me.ListBox1.List(0, 2) = "Company"
Me.ListBox1.List(0, 1) = "Description"
Me.ListBox1.List(0, 3) = "Cost"
Me.ListBox1.Selected(0) = True
For I = 2 To Sheets("Prices").Range("D1000").End(xlUp).Row
For X = 1 To Len(Sheets("Prices").Cells(I, 2))
A = Me.TextBox1.TextLength
If LCase(Mid(Sheets("Prices").Cells(I, 2), X, A)) = Me.TextBox1 And Me.TextBox1 <> "" Then
Me.ListBox1.AddItem Sheets("Prices").Cells(I, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "" & Sheets("Prices").Cells(I, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Format(Sheets("Prices").Cells(I, 14), "£#,00.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "" & Sheets("Prices").Cells(I, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "" & Sheets("Prices").Cells(I, 4)
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "" & Sheets("Prices").Cells(I, 16)
ElseIf LCase(Mid(Sheets("Prices").Cells(I, 4), X, A)) = Me.TextBox1 And Me.TextBox1 <> "" Then
Me.ListBox1.AddItem Sheets("Prices").Cells(I, 1)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "" & Sheets("Prices").Cells(I, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Format(Sheets("Prices").Cells(I, 14), "£#,00.00")
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "" & Sheets("Prices").Cells(I, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "" & Sheets("Prices").Cells(I, 4)
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "" & Sheets("Prices").Cells(I, 16)
End If
Next X
Next I
End Sub
Last edited by a moderator: