excel vba userform enableevents

Question

I'm having a problem with Excel VBA UserForm Events in Office Excel 2013 as follows

Simple userform with three check boxes (CB1,2,3) and two buttons Cancel and OK
When checking CB1 set CB3 = false
When checking CB2 set CB3 = false
When checking CB3 set CB1 = false and CB2 = false

I have read and understood http://www.cpearson.com/excel/SuppressChangeInForms.htm regarding the suppression of UserForm Events and to part it works...

In the list above 2. and 3. above work correctly in code (shown below) and no-events are fired for CB3. However when I do 4. Check CB3 - it fires events for CB1 and CB2, even though I have set it to not fire events.

Any help gratefully received,

Best regards

Seán

Code:

Public EnableEvents As Boolean
Private Sub UserForm_Initialize()

    Me.EnableEvents = True

End Sub

Private Sub vboInputsSelected_Click()

    Me.EnableEvents = False
    vboPracticesSelected.value = False           'this line does NOT fire an event
    Me.EnableEvents = True

End Sub

Private Sub vboOutputsSelected_Click()

    Me.EnableEvents = False
    vboPracticesSelected.value = False           'this line does NOT fire an event
    Me.EnableEvents = True

End Sub

Private Sub vboPracticesSelected_Click()

    Me.EnableEvents = False
    vboInputsSelected.value = False           'this line DOES fire an event
    vboOutputsSelected.value = False          'this line DOES fire an event
    Me.EnableEvents = True

End Sub

Do you not need to set each check box's value instead of just assigning true or false to the object? — NickSlash
Thanks Nick, good point, I have changed the code to be correct: with the .value on the end (vboInputsSelected.value = False) and it STILL fires an event when EnableEvents is false — Sean

usncahill usncahill · Accepted Answer · 2013-10-27T14:07:01

This works well for me. The If bails out when an event is in progress. Realize that the EnableEvents variable does nothing on its own to prevent events. It is only a boolean you created. You need to check it, before allowing an event to occur, for it to do anything.

Public EnableEvents As Boolean

Private Sub vboInputsSelected_Click()
  If Not EnableEvents Then Exit Sub

  Me.EnableEvents = False
  vboPracticesSelected.Value = False
  Me.EnableEvents = True
End Sub

Private Sub vboOutputsSelected_Click()
  If Not EnableEvents Then Exit Sub

  Me.EnableEvents = False
  vboPracticesSelected.Value = False
  Me.EnableEvents = True
End Sub

Private Sub vboPracticesSelected_Click()
  If Not EnableEvents Then Exit Sub

  Me.EnableEvents = False
  vboInputsSelected.Value = False
  vboOutputsSelected.Value = False
  Me.EnableEvents = True
End Sub

excel vba userform enableevents

1 Answers