Application.EnableEvents doesn’t work in a Userform’s module. If you want to temporarily disable events in Userforms, you have to do it manually. One way is to use a module-level Boolean variable. You set this variable to True when you want events disabled and wrap all your event code in an If statement to test the variable’s value.
In this example, there is a userform with two textboxes. Typing in TextBox1 is duplicated in TextBox2. If you type directly in TextBox2, you’ll see a message box for everything you type. But because we disable events with a module-level variable, typing in TextBox1 does not produce the message box, even though it changes TextBox2.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Option Explicit Dim mbEvents As Boolean Private Sub TextBox1_Change() mbEvents = True Me.TextBox2.Text = Me.TextBox1.Text mbEvents = False End Sub Private Sub TextBox2_Change() If Not mbEvents Then MsgBox Me.TextBox2.Text End If End Sub |
I use a slightly different approach. I want my code to assume that events are enabled by default. I only disable events when I want to temporarily prevent an event from firing, e.g. when manipulating a control programmatically, after which events are re-enabled. Additionally, I always test whether events are enabled at the top of all event handlers. So:
Option Explicit
Dim mbEvents As Boolean
Private Sub UserForm_Initialize()
mbEvents = True
End Sub
Private Sub TextBox1_Change()
If Not mbEvents Then Exit Sub
mbEvents = False
TextBox2.Text = Me.TextBox1.Text
mbEvents = True
End Sub
Private Sub TextBox2_Change()
If Not mbEvents Then Exit Sub
MsgBox TextBox2.Text
End Sub
It requires a slightly more coding but I find it more intuitive because it follows Excel’s Application.EnableEvents logic and avoids the ‘converse boolean name’ situation e.g.
If Not mbNotEnabled Then
‘cos my brain is too simple!
Jamie: I’ve always hated how my method was backward. The extra coding is nothing compared to the extra thinking I have to do every time I use mine. Thanks.
Wonderfull – I’d been trying to deal with this for a while (in my case altering a listbox when the selection in the listbox is changed). Problem solved in 30 seconds…
The code you have given is helpful for the listbox if you are making teh changes in other list box.
But it is not working fine if the changes are made within the same listbox.
I want to change the selection of the Listbox1 within the ListBox1_Click or ListBox1_Change.
Excellent solution to a difficult problem. Thanks.
Why create a boolean when you can use application.enableevents to accomplish the same thing? The bit stays flipped between subroutines.
EnableEvents doesn’t work for me in userforms. I still use 2010 so maybe they fixed it. But it would be the first VBA bug fix since Excel 97.
I mean it works and thank you, but can some one explain to me how??
i don’t see how the variable created has anything to do with Application.EnableEvents as it’s never defined as such.
what kind of vodoo magic is this???
FYI @Dick Kusleika still an issue in Office365!
It doesn’t have anything to do with EnableEvents. It just tries to replicate what EnableEvents does when it’s used outside of userforms.
In TextBox1_Change, the line mbEvents = True is like setting EnableEvents = False (because of how we use it later).
Then in TextBox2_Change, If Not mbEvent Then is doing whatever Excel does behind the scenes when it checks to see if EnableEvents was set (and what Excel doesn’t do when inside a userform).
Because Excel does that behind the scenes when you’re not in a userform, you don’t have to go to every event and check to see if you set EnableEvents. With this method, you’re doing all the work, so in every event, you have to check if you set the variable. This example is simple, but if you had a ton of events, you have a lot of extra code to write.