selected checkbox deselects other checkboxes and vice versa

[looping]

Hi everyone,

I have an excel sheet with three checkboxes, of which one is selected (checkbox.value = true) and the others not. Selecting one checkbox shall deselect the other ones (and do some other stuff that is not relevant here).

I tried this:

Private Sub checkbox1_Click()
    checkbox1.value = True
    checkbox2.value = False
    checkbox3.value = False
End Sub

My problem is that checkbox_Click() is also triggered if the checkbox-value is changed via VBA, resulting in a loop with the checkboxes selecting and deselecting each other.

So, I am looking for an alternative Trigger event for the Sub, but I can't think of anything that would work.

Thanks in advance.

 

[sous2817]

You could try subsetting the conditions with an IF statement. Something like:

Private Sub checkbox1_Click()
If  checkbox1.value = True Then
    checkbox2.value = False
    checkbox3.value = False
end If
End Sub

Private Sub checkbox2_Click()
If  checkbox2.value = True Then
    checkbox1.value = False
    checkbox3.value = False
end If
End Sub

Private Sub checkbox3_Click()
If  checkbox3.value = True Then
    checkbox1.value = False
    checkbox2.value = False
end If
End Sub

That should keep everything from re-triggering.

 

[looping]

Thanks! There are no loops any more. (You can deselect all checkboxes now, but I can live with that)

 

[sous2817]

If you don't want to be able to deselect all of the checkboxes, maybe you should use a radiobutton...

 

[baitmaster]

The problem is caused by events triggering each other, a well-known phenomenon, and cause of many a head-ache

use application.enableevents = false at the start of your routine, then reapplying at the end. Good discipline on this feature will save you many problems in future

 

[sous2817]

I could be wrong, but doesn't application.enableevents only apply to worksheet-level events? I don't think that would work on a userform. I guess I could just go and test it, but that's my gut feeling.

 

[Steeviee]

This may be totally irrelevant, but if you use optionbuttons instead, they have this property already (selecting one deselects the others). You need to make sure that they all have the same GroupName in the properties (this is important if you have more than one set).

 

[looping]

Thanks for the replys (we live and learn )

I think I will go for the option buttons.