ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Hi,
I am using the code shown below which works very well.
Currently i type in TextBox1 & the codes looks for a match on my worksheet & places them in ListBox1
I am finding that should i type a little slow the code runs & takes forever to load the matched items in the ListBox1
Example
I need to type REMOTE but say there is a split delay in type the RE the code just sees the R & its now a waiting game.
I dont wish to type then press a command button to run the code hence a delay so at least i can type whats needed.
Is this possoble.
Thanks.
I am using the code shown below which works very well.
Currently i type in TextBox1 & the codes looks for a match on my worksheet & places them in ListBox1
I am finding that should i type a little slow the code runs & takes forever to load the matched items in the ListBox1
Example
I need to type REMOTE but say there is a split delay in type the RE the code just sees the R & its now a waiting game.
I dont wish to type then press a command button to run the code hence a delay so at least i can type whats needed.
Is this possoble.
Thanks.
Rich (BB 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("POSTAGE")
sh.Select
With ListBox1
.Clear
.ColumnCount = 6
.ColumnWidths = "240;100;250;50;150;100"
If TextBox1.Value = "" Then Exit Sub
Set r = Range("C8", Range("C" & 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 'Item
.List(i, 1) = f.Offset(, -2).Value 'Date
.List(i, 3) = f.Row 'Row Number
.List(i, 2) = f.Offset(, -1).Value 'Customers Name
.List(i, 4) = f.Offset(, 6).Value 'Ebay User Name
.List(i, 5) = f.Offset(, 1).Value 'Info
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value 'Item
.List(.ListCount - 1, 1) = f.Offset(, -2).Value 'Date
.List(.ListCount - 1, 3) = f.Row 'Row Number
.List(.ListCount - 1, 2) = f.Offset(, -1).Value 'Customer Name
.List(.ListCount - 1, 4) = f.Offset(, 6).Value 'Ebay User Name
.List(.ListCount - 1, 5) = f.Offset(, 1).Value 'Ebay User Name
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
TextBox1 = UCase(TextBox1)
.TopIndex = 0
Else
MsgBox "NO SOLD ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET SOLD ITEM SEARCH"
TextBox1.Value = ""
TextBox1.SetFocus
End If
End With
End Sub
Private Sub ListBox1_Click()
Set sh = Sheets("POSTAGE")
sh.Select
Range("C" & ListBox1.List(ListBox1.ListIndex, 3)).Select
Unload PostageItemSoldSearch
End Sub