CODEWITHSUNDEEP
exceleventsvba
Bruder
Bruder

Reputation: 573

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

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!

Upvotes: 4

Views: 10980

Answers (2)

Benny Law
Benny Law

Reputation: 21

reference: http://www.cpearson.com/excel/SuppressChangeInForms.htm

To suppress events in a form, you can create a variable at the form's module level called "EnableEvents" and set that to False before changing a property that will cause an event to be raised.

Public EnableEvents As Boolean
Private Sub UserForm_Initialize()
    Me.EnableEvents = True
End Sub

Sub Something()
   Me.EnableEvents = False
   ' some code that would cause an event to run
   Me.EnableEvents = True
End Sub

Then, all of the controls on form should have a test if that variable as their order of business in any event code. For example,

Private Sub ListBox1_Change()
    If Me.EnableEvents = False Then
        Exit Sub
    End If
    MsgBox "List Box Change"
End Sub

You can declare the EnableEvents as Private if only procedures with that form need to suppress events. However, if you have forms that are programmatically linked together, such UserForm2 adding an item to a ListBox on UserForm1, you should declare the variable as Public and set it for another form with code like the following:

UserForm1.EnableEvents = False
'
' change something on UserForm1
'
UserForm1.EnableEvents = True

The primary difference between the EnableEvents property and code shown above and the Application.EnableEvents property is that with a UserForm EnableEvents, all control on the form must have code to exit if EnableEvents is True. In other words, all the form's controls must cooperate and respect the setting of EnableEvents.

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38551

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.

Upvotes: 4

Related Questions