VBA to prevent a change event from triggering if another change event triggers

Question

this one may be impossible to solve in VBA but I'd like to see what you experts have to say about it.

I have a textbox on a userform that triggers a macro within a TextBox1_Change() type of sub.

If the user types "ABC" in the textbox, the macro gets triggered 3 times: once for "A", once for "AB" and once for "ABC". This macro is actually kind of heavy, so I would like it to run only when the user is actually done typing, and not inbetween single key strokes.

I know I can make the user "press enter" or whatever and only then run the macro, but this is not what I'm looking for. I want him to type freely and see the results of his typing dynamically show up, with no other type of interaction required.

So, I came up with the idea of making the change event wait and see if another change event gets triggered within, say, 1 second from the first. If that happens, the first change event aborts.

Now this would work, and I think I would know how to code it, except that I don't know how to give the user the power to keep typing even when the first change event is running.

What I mean is that when the first macro runs, it "freezes" everything. Waiting to see if another change event triggers will therefore not work, as nothing is going to trigger until the first macro is done running.

Do you guys see my problem here? How would you go about this? Any chance I can achieve the results I'd like?

Any help is greatly appreciated, thanks guys!

Answer 1

I tested the following, and it works (assuming I correctly understand what you're trying to do).

In a code module, write this:

Public aRunIsScheduled As Boolean
Public nextRunTime As Variant

Sub MyMacro()
    'Flag macro as having been run, no longer scheduled.
    aRunIsScheduled = False

    'Place your macro code here.
    'I'll just use some dummy code:
    MsgBox "MyMacro is running!"

End Sub

In your sheet module:

Private Sub CommandButton1_Click()

    If aRunIsScheduled Then
        ' Cancel the previously scheduled run.
        Application.OnTime EarliestTime:=nextRunTime, _
            Procedure:="MyMacro", Schedule:=False
        aRunIsScheduled = False
    End If

    ' Schedule a new run 3 seconds from now:
    nextRunTime = Now + TimeValue("00:00:03")
    Application.OnTime EarliestTime:=nextRunTime, _
            Procedure:="MyMacro", Schedule:=True
    aRunIsScheduled = True

End Sub

I put a Commandbutton in my sheet and here I'm using its change event, but you can put this code in your TextBox1_Change() event instead, in exactly the same way.