0 votes
in VBA by Beginner (26 points)

Every time I see this line of code

“Application.EnableEvents = False”

in an event macro at the beginning, I wonder whether there is any contradiction in using it in an event macro as the whole purpose of an event macro  is to trigger on the occurrence of a specified event. Please clarify my doubt.

3 Answers

+1 vote
by Beginner (56 points)
selected by
 
Best answer

In certain circumstances, you may need to use Application.EnableEvents = False when you have a routine set to run on a worksheet event such as a Worksheet_Change event. 

As an example, if the Worksheet_Change event macro you've written actually makes a change to the worksheet, then it would cause another Worksheet_Change event and fire your macro again and again in an endless loop. In this case, using Application.EnableEvents = False within your routine would make sure that your Worksheet_Change event macro fired only once with the first change. 

You would subsequently use the corresponding command of Application.EnableEvents = True at the appropriate point in the macro to re-enable normal event behavior thereafter. I hope this answers your question. Good luck.

by Skilled (267 points)
Well, I learned something new. I always used a boolean variable that I called Busy. I never knew there was a built-in feature like Application.EnableEvents.

Thanks for sharing this excellent answer!
by Beginner (56 points)
Thanks ParserMonster.. I learned this one the hard way.. thus my comment about the endless loop.. haha!! I like your boolean variable option.. clever solution. There are always many ways to solve problems using VBA. Cheers!
by Skilled (267 points)
I know, right? "Out of Stack Space." Bah!

LOL
by Beginner (26 points)
edited by
Dear ParserMonster

Would you show me how you used the boolean variable!
by Skilled (267 points)
+1

Abdan, here's how I would have done the example presented by YasserKhalil:

Option Explicit

Private Busy As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Busy Then
        Exit Sub
    End If
    If Target.Address = [A1].Address Then
        Busy = True
        Target.Offset(, 1) = Target.Value
        Target.ClearContents
        Busy = False
    End If

End Sub

Two points to keep in mind:

  • The Boolean variable must have module-level scope
  • You may be tempted to wrap the event code between If Not Busy Then ... End If which will work; however, your code will not be as clear, especially if the event code is long.
Just for fun, here is the wrapped version, along with a couple of Debug.Print statements to really see what's going on (Switch to code view after changing A1):
Option Explicit

Private Busy As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "entered ";
    If Not Busy Then
        If Target.Address = [A1].Address Then
            Busy = True
            Target.Offset(, 1) = Target.Value
            Target.ClearContents
            Busy = False
        End If
    End If
    Debug.Print "left ";
End Sub

The Immediate Window should display:

entered entered left entered left entered left entered left left 

 

+1 vote
by Skilled (605 points)

Great answer WayneEdmo. As an example to what you have explained in words, if you have such a code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [A1].Address Then
        Target.Offset(, 1) = Target.Value
        Target.ClearContents
    End If
End Sub

This code will be affected only when you change A1 cell and it will move the contents of A1 to B1 then to clear the cell contents .. At the clear contents the event will be triggered again and B1 will be empty and the target cell which is A1 will be cleared again and the event will be triggered and so on (this is the endless loop) and this would crash excel.

So it is very important to put the line of disabling the events before the line that will cause the trigger of the event again.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [A1].Address Then
        Target.Offset(, 1) = Target.Value
        Application.EnableEvents = False
            Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

Regards

by Beginner (56 points)
Thanks YasserKhalil.. I meant to come back and provide some example code. You've got it covered. Cheers!
by Skilled (605 points)
You're welcome. We all learn from each other and I have put the example according to your great explanation.
0 votes
by Skilled (279 points)
Good answers here and it's also worth remembering that the Application.EnableEvents property ONLY works on worksheet and workbook events.  This means that you will need something like ParserMonster's global variable solution if you're trying to control events for things such as a userform control (like when a user changes selection in a list box).

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...