VBA to prevent change event from triggering if another change event fires
it cannot be solved in VBA, but I would like to know what the experts have to say about it.
I have a textbox on a custom form that runs a macro of type Saturday TextBox1_Change ().
If the user types "ABC" in the text box, the macro runs three times: once for "A", once for "AB", and once for "ABC". This macro is actually heavy, so I would like it to only run when the user actually typed, not between the strokes of the same key.
I know I can force the user to press Enter or something and then run the macro, but that's not what I'm looking for. I want him to print fluently and see the results of his input dynamically displayed, without any other interaction.
So I figured out how to make the change event wait and see if another change event is triggered, say 1 second from the first. If this happens, the first change event is canceled.
Now this will work, and I think I know how to code it, except I don't know how to let the user keep typing even when the change event is fired for the first time.
I mean, when the first macro runs, it freezes everything. Waiting to see if other change event triggers are firing as nothing will be fired until the first macro runs.
Can you guys see my problem here? How would you do it? Is there a chance that I can achieve the results I would like?
Any help is greatly appreciated, thanks guys!
source to share
I have tested the following and it works (assuming I understand correctly what you are trying to do).
In the 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 leaf 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 Commandbutton in my sheet and here I am using its change event, but you can put this code in your TextBox1_Change()
event, exactly the same.
source to share