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.

6 thoughts on “Disabling Events In Userforms

  1. 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!


  2. 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.

  3. 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…

  4. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *