Apply delay before running code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,826
Office Version
  1. 2007
Platform
  1. Windows
Code in use is shown below
I would like to apply a delay of say 5 seconds before the code runs.
I tried Sleep 5 at the begining but did not work for me



VBA Code:
Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  
    Dim response As Integer
    Dim oNewRow As ListRow

' if combo empty stop here
If Len(Me.ComboBox1.Value) = 0 Then Exit Sub

' is entry in drop down
If Not Me.ComboBox1.MatchFound Then
    ' ask if to add entry to list
    response = MsgBox("VEHICLE ISNT IN VEHICLE LIST" & vbCrLf & vbCrLf & "DO YOU WISH TO ADD IT ?", vbYesNo + vbCritical, "ADD VEHICLE TO LIST")
    
    If response = vbYes Then
        
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        ' add row to table
        With Sheets("INFO").ListObjects("Table2")
            Set oNewRow = .ListRows.Add
            ' put what was entered into list
            oNewRow.Range.Cells(1) = Me.ComboBox1.Value
            '** SORT A=Z HERE **
            .Sort.SortFields.Clear
            .Sort.SortFields.Add KEY:=.ListColumns(1).Range, SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With .Sort
                .Header = xlYes
                .Apply
            End With
            Application.Goto (.HeaderRowRange.Cells(1))
        End With
                
        ' re-activate QUOTES sheet
        Sheets("QUOTES").Select
        
        ' assign the new list to combo
        With Me.ComboBox1

            .List = Sheets("INFO").ListObjects("Table2").DataBodyRange.Value
        End With
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
  
    Else    ' response was NOT vbYes
       MsgBox Me.ComboBox1.Value & vbCrLf & "THE VEHICLE SHOWN ABOVE" & vbCrLf & "WILL NOT BE ADDED TO VEHICLE LIST", vbInformation, "VEHICLE WONT BE ADDED TO LIST MESSAGE"
        ' clear entry
        Me.ComboBox1.Value = ""
        ' maintain focus
        Cancel = True
        Exit Sub
    End If
    
End If

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
The Sleep argument is milliseconds, so you need Sleep 5000.

Of course, you need the API declaration at the top of the module, above other procedures:

VBA Code:
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
#End If
 
Upvote 0
This is used on another sheet but but i get a not defind message when i try & add to the code supplied above.
I just tried the part in Red,please advise how i can use it in the code in first post


VBA Code:
Private Sub TextBox7_Change()
    TextBox7 = UCase(TextBox7)
[COLOR=rgb(184, 49, 47)]   For i = 1 To 600
DoEvents
Sleep (10) ' THIS DELAY ALLOWS THE USER TO SELECT THE REMOTE TYPE
Next i[/COLOR]
End Sub
 
Upvote 0
In a Module i put the following for my 32bit Pc running Windows 7 Professional.
The 2000 is 2 seconds.

VBA Code:
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
Sub Delay()
Sleep 2000
End Sub


On the code for the Combobox in question i did the following.

Call Delay

Now the 2 second delay takes place & then the code runs.
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,470
Members
452,915
Latest member
hannnahheileen

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