Add delay to code before the code actually run

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. 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.


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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Still watching and therefore a note.
@ipbr21054 apparently you are talking about controls on a userform, which don't have a LostFocus event but Exit event.
@JLGWhiz apparently you are talking about controls upon a worksheet.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
That is what ive done see screen shot
 

Attachments

  • 851.jpg
    851.jpg
    46.3 KB · Views: 4

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
So using the ENTER option on the right drop down for TextBox1 i now have this.
With all the opther code but after i type & press Enter i assume it should then run but it doesnt.

Cant see why this is such an issue for me

Rich (BB code):
Private Sub TextBox1_Enter()
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
This is what i needed.

Rich (BB code):
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Still watching and therefore a note.
@ipbr21054 apparently you are talking about controls on a userform, which don't have a LostFocus event but Exit event.
@JLGWhiz apparently you are talking about controls upon a worksheet.
Based on the OP, textbox1 is on a worksheet and is an Active-X textbox. Tx for your input. JLG
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
This is what i needed.

Rich (BB code):
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
Perseverance pays off! :cool:
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
All sorted now.
Thanks for all the inputs here.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
happy to assist,
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,119,011
Messages
5,575,541
Members
412,676
Latest member
Otterbox
Top