Add delay to code before the code actually run

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
The line with
VBA Code:
TextBox1 = UCase(TextBox1)
is causing your code to run twice on every key stroke.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top