Solved: UserForm Checkbox Problem

[CreganTur]

I've got a UserForm that makes use of a group of check boxes that will enter data into a form letter based on the User's choices.

There are 2 main checkboxes: "Reinstate" and "No Reinstatement." All of the checkboxes except for these 2 are .enabled = False when the UserForm Loads. if the User Selects "Reinstate" then everything remains exactly as it is. If the User selects "No Reinstatement" then the disabled checkboxes become .Enabled = True (and the Reinstatement checkbox is unchecked if it was previously checked).

I've noticed some very odd behavior occurring if I clicked on one, and then the other. I was forced to double-click on the checkboxes to actually set them to True. From stepping through the code I discovered that when, for example, "Reinstatement" changes the value of "No Reinstatement", that the code behind "No_Reinstatement_Click" would fire.

I've attached an example doc that shows exactly what's going on. Just click back and forth between "Reinstatement" and "No Reinstatement" and you'll see what I mean.

I want to know if there's a way to keep this from happening.

I did find a workaround- I changed "Reinstatment" and "No Reinstatement" to Option Buttons, instead of CheckBoxes. This stopped the problem I mention above... but I'd still like to know if there's a way to stop this from happening with the checkboxes.

TIA

[fumei]

You should more than likely be using the _Change events, rather than _Click.

Please describe precisely the behavior you want.

N.B. put a commandbutton on to exit.

[MOS MASTER]

Hi, :hi:

This is a very old problem that lacks a good mechanisme in VBA. (AFAIK)
The problem is that one event triggers another event. So if you are in the clickevent of checkbox a and that code set's the enabled property of Checkbox b to true the Click eventhandler for that code will run.

If you have a program language like C# (or vb.NET) for instance you have the possibility to unregister eventhandlers (detach) at the beginning of the code and at the end re-attach them.

But we have to work with VBA so that leaves us creating some mechanisme with Boolean variabels to control the events.

In your case you could use something like this: (and there could be so much variations)

Option Explicit
Private bProcessing As Boolean
Private Sub optNoReinstate_Click()
If Processing Then Exit Sub
bProcessing = True
If Me.optNoReinstate = True Then
Me.opt18mos.Enabled = True
Me.optConcealed.Enabled = True
Me.optDestroy.Enabled = True
Me.optFraud.Enabled = True
End If
If Me.optReinstate = True Then
Me.optReinstate = False
Exit Sub
End If
bProcessing = False
End Sub
Private Sub optReinstate_Click()
If Processing Then Exit Sub
bProcessing = True
'disable No Reinstatement Reasons
If Me.optReinstate = True Then
Me.opt18mos.Enabled = False
Me.optConcealed.Enabled = False
Me.optDestroy.Enabled = False
Me.optFraud.Enabled = False
End If
If Me.optNoReinstate = True Then
Me.optNoReinstate = False
Exit Sub
End If
bProcessing = False
End Sub
Function Processing() As Boolean
If bProcessing = True Then
bProcessing = False
Processing = True
End If
End Function


HTH

[MOS MASTER]

You should more than likely be using the _Change events, rather than _Click.

Gerry,

The behaviour will be the same for the change event. In the code the .Enabled property is changed and therefore both the Click event as Change evenhandler will be triggerd.

But I'd love to see a better way to deal with this. (Like I stated above for C#)

[CreganTur]

Wow. Just... wow. This fix works perfectly.

I don't understand how it works or why yet, but I'll try to figure that out on my own time.

[MOS MASTER]

Hi Randy, :yes

Glad to see you got it working now.
To fully understand what's going on you need to learn how to debug your code.

If I where you I'd take your old code and put a breakpoint (F9 on a code line) on each sub and run your form.
When you click a checkbox your code will stop at the breakpoint. Then use F8 to step through your code.

This way you'll see the how the code runs and you see the raise condition that occurs when the events start firing eachother. (that you didn't expect)

later on you'll learn to use all the other debugging windows like:
Imediate
Locals
etc...

Have fun debugging!!! :whistle: