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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
Maybe you could use one of the other events to activate he macro, instead of 'Change'. For instance, LostFocus would trigger the macro when you have finished typing and click outside the textbox.


1605042206401.png
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
OK
Didnt think of that ,will take a look.

Thanks
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
The line with
VBA Code:
TextBox1 = UCase(TextBox1)
is causing your code to run twice on every key stroke.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok
I will look at deleting that.
With regards delay I did see a short line of code which basically looked at current time & the x amount of seconds delay.
It worked in respect of the x amount of delay but still couldn’t wait to run if I didn’t type quick enough.
Maybe I put it in the wrong place ?
I’m looking to be able to type in the text box then a delay say 3 seconds before the search / load listbox takes affect.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
With regards delay I did see a short line of code which basically looked at current time & the x amount of seconds delay

I don't think a delay will do any good with the TextBox_Change event. Unlike a cell on a sheet, the text box change occurs when you type, not when you click or press enter.. So when you type the first letter in the textbox, the change event is triggered and the macro is activated. But with LostFocus, the event occurs only when you leave the text box with the mouse pointer or Tab. You can use the same code you have now, just change the title line form TextBox1_Change to TextBox1_LostFocus. Then the line that @GWteB pointed out would not be a problem for you.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Is there such a thing where I would type what I require then press Return to the run the code.

If I need to click out of the text box etc then might as well have the command button. But this would be a last option.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
If you used a cell on the worksheet for your data entry, then the Worlksheet_Change event would work like you want the textbox to work.. But what you see in post #2 is pretty much it for textbox events.. There are numerous options for triggering code to run, depending on the consistency of the input data (i.e. same set of values repeated periodically) you could use drop down lists, or inputbox. But each tool has its advantages and its drawbacks. You would need to do some web searching and reading to determine your best resource for what you want to do.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I didnt even see LostFocus in the drop down option,i ven wrote it like you had shown but i type in the TextBox & click outside of it but nothing happens at all.

Can you show me the code copied from you pc please

I am using 2007 version so maybe not supported ?

Thansk
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,762
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Private Sub TextBox1_LostFocus()
'Your code here
End Sub

When you create your text box from the Active-X toolbox under the Developer::Insert options on the ribbon, just right click on the textbox image once you have it in position and still in design mode, then click 'View Code'. That will open the code module with the options for the control that called it (in this case the textbox). The default macro title will be displayed. To select a different macro title line, click the narrow box at right top of the code module pane and a dropdown list will show you all option. (See post #2). Click the option you want and Excel will automatically display the correct title line of code for the selected option.

The vb editor has lots of features to help you with code. It might be beneficial if you learn to use the tools for creating and editing code. There are tutorials on line for using the editor.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,119,012
Messages
5,575,545
Members
412,677
Latest member
Davejf81
Top