CODEWITHSUNDEEP
vbaexcelcombobox
EndUserAbuser
EndUserAbuser

Reputation: 99

Wait until user has stopped typing in ComboBox to run macro (VBA)

I'm programming a sort of cross-reference database. An ID is generated based on the document name chosen or created.

The ComboBox I've referred to in the title acts on change (after 3 letters), checks the database for similar entries to what was typed, and displays the drop downof options that match. Once an entry is picked from the match list or a new name created - the appropriate number is generated.

Since the DropDown list is generated after every letter is typed, it takes a while to type what you want. I want to wait a few seconds after the last change to run the macro.

Any Ideas on how I can accomplish this?

Upvotes: 2

Views: 3860

Answers (2)

Tehscript
Tehscript

Reputation: 2556

An alternative using Application.OnTime again:

In Userform:

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    StartTimer
End Sub

In Module:

Public RunTime As Double

Public Sub StartTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunTime, Procedure:="YourCode", Schedule:=False
    RunTime = Now() + TimeValue("00:00:03")
    Application.OnTime RunTime, "YourCode"
End Sub

Public Sub YourCode()
    MsgBox "It's working!"
End Sub

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

This is a bit tricky as VBA doesn't support multi-threading. But we can use the Application.OnTime event to trigger a test in the future to test if the last key event is at least 3 seconds ago.

In a Module insert:

Option Explicit

Public LastKeyEvent As Date
Public Const WaitTimeValue As String = "00:00:03" 'test for key event in 3 seconds

Public Sub TestKeyEvent()
    'test if last key event is at least 3 seconds ago. 
    'If so: run your search or message box 
    'If not: do nothing
    If LastKeyEvent <> 0 And LastKeyEvent + TimeValue(WaitTimeValue) <= Now Then
        LastKeyEvent = 0 'Ensure this is only triggered once:
                         'If we don't do this and multiple keys are pressed within 1 second 
                         'then it would run multiple times.

        MsgBox "3 seconds without keypress, we can start search"
        'start your search here (instead of message box) …
    End If
End Sub

Now you can use for your textbox change event eg TextBox1:

Private Sub TextBox1_Change()
    Dim alertTime As Date

    LastKeyEvent = Now 'remember when the last key event was
    alertTime = LastKeyEvent + TimeValue(WaitTimeValue)
    Application.OnTime alertTime, "TestKeyEvent" 'run TestKeyEvent in 3 seconds
End Sub

Note:
This is a workaround that works for 2 or more seconds. But does not for less then 2 seconds.

Upvotes: 1

Related Questions