How to avoid Recursive Event Triggering between Controls

[lanassist]

Basically : How to program controls on a form so that if they are synchronised with each other, the updating of one control does not create a domino effect of recurring updates between the two or more controls.

Eg: Listbox and Spinbutton synchronised through a public variable.
The onchange event with a click on the listbox should update the spinbutton value without this conseguent change in the spinbutton returning an update to the listbox. (and viceversa)
ie. How to switch off the controls events for a moment.

I am stuck with finding an elegant solution. I managed to get through in part using boolean flags but I realise this is not the way.
I believed that this could be done without classing the controls however options like Application.EnableEvents=False does not work.
[This is an MsOffice App mainly through VBA forms.]

Any help/examples would be most appreciated
Thanks

 

[GlennUK]

Are you sure that Application.EnableEvents = False doesn't work?
You say ...

The onchange event with a click on the listbox should update the spinbutton value

Please show the code for this( with the Application.EnableEvents = False in please ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rmikesmith]

Glenn,

Applicatione.EnableEvents = False

has no effect on Userform controls.

In my own code, I use a module-level boolean variable (the Userform module) that I set to true at the beginning of an event procedure and False at the end. I then check for this variable inside other event procedures and exit if True. Here is some example code, associated with a SpinButton control and a ListBox control on a Userform. Assume that when the SpinButton control changes, it populates the Listbox with new entries (not shown). The ListBox's Change event also carries out some action, however. This is intended to execute only when the user makes a change so we don't want it to fire when it is being re-populated.

Dim EventActive As Boolean

Private Sub ListBox1_Change()
   If EventActive Then Exit Sub
' ... Other code here
End Sub

Private Sub SpinButton1_Change()
   EventActive = True
'... Other code here
   EventActive = False
End Sub

Regards,
Mike

 

[combo]

The AfterUpdate event can be a solution for most of VBA controls on a userform. For the rest and in case of location on a document the flag seems to be the only solution, this value will emulate EnableEvents setting:

Private bFormEnableEvents As Boolean

Private Sub ScrollBar1_Change()
If bFormEnableEvents Then
    bFormEnableEvents = False
    SpinButton1.Value = ScrollBar1.Value
    bFormEnableEvents = True
End If
End Sub

Private Sub SpinButton1_Change()
If bFormEnableEvents Then
    bFormEnableEvents = False
    ScrollBar1.Value = SpinButton1.Value
    bFormEnableEvents = True
End If
End Sub

Private Sub UserForm_Initialize()
bFormEnableEvents = True
End Sub

combo

 

[lanassist]

My apologies for delay in responding. (Absent for tech support.)
Thank you for the ideas.

However :
Mike confirms my doubts on the App option.
And also on the tendency of flags.
I doubt that this can be the real solution.
There should be a command to deactivate a controls event response when one needs to and then wake it up again when necessary. I thought I had read on this some time ago but fail to recall where.

My aim was to be able to do the following 'without generating any unnecessary events/loops' or recursion.

Click on the listbox items (generates listindex change event etc) which should update the spinbutton value, generating a spinbutton change event (which would recall the update listbox procedure.
Similarly click up or down on the Spinbutton would update the position (listindex) of the listbox (note that referencing the listindex generates a listbox change event just as changing the spinbutton value creates a spinbutton change event. Obviously the listbox change event recalls the update procedure of the spinbutton control and this could in turn recall the update procedure needlessly unless one can identify the caller/changer mechanism. Anyway more than once is unnecessary
My attempt at using flags does not really appeal to me.
There should be a better, more elegant way to do this.

Combo proposes that there is no other solution than this.
Of course going to the API's should do it but is there really nothing else to do ?

I will examine his code sample now using listboxes.
Thanks

 

[combo]

Actually, I pointed to AfterUpdate event, for controls on the userform, that is raised only by UI change. You can freely change controls by code as long as you have no 'Change' or 'Click' events handled, without chain of events:

Private Sub ScrollBar1_Change()
Me.TextBox1.Value = Me.ScrollBar1.Value
End Sub

Private Sub TextBox1_AfterUpdate()
MsgBox "change"
End Sub

There is no regular VBA solution, certainly you can do it with API and subclassing, but wonder if it will make code more elegant.

combo

 

[GlennUK]

Hi rmikesmith , d'oh ( smacks forehead ), yeah I remember now about Application.EnableEvents having no effect on Userform controls now. ( skulks off to take some Gingko Biloba to help memory function better ).


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rmikesmith]

Glenn,

No need to skulk. Wear that Gingko proudly on your cerebrum

Mike

 

[combo]

Another way to switch on/off single object events, however in case of vba controls limited to specific class events, could be WithEvents declaration in UserForm module. Pointing to a control or setting to Nothing, with events handled by this variable, could simplify the code. I wrote a four part faq concerning events in this forum.

combo

 

[lanassist]

Hi Combo,
In fact I had been thinking of this alternative (WithEvents)
(This method is not far from making a custom object class)
Please leave a note of your FAQ link.
In the meantime I managed to make the simple combination (Listbox/SpinButton) relatively successful, limiting the 'Change' or 'Click' events handling, as you seem to have commented on in a preceding post.
Thanks

 

[combo]

They are faq707-4973, faq707-4974, faq707-4975 and faq707-4976. Probably the third could be most interesting. All faqs are available via Forum FAQs link (above thread).

combo