Disabling Events In Userforms
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.
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.