Loop for check boxes

[Karissa]

Below is the code I have. i haven't written code in a long time and can't
remember how to write a loop for this - i have about 20 sets of four check
boxes and don't want to do this for every one of them - Thanks to any one who
can help

------
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
CheckBox2 = False
CheckBox3 = False
CheckBox4 = False
End If
End Sub




Private Sub CheckBox2_Click()
If CheckBox2 = True Then
CheckBox1 = False
CheckBox3 = False
CheckBox4 = False
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox3 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox4 = False
End If
End Sub

Private Sub CheckBox4_Click()
If CheckBox4 = True Then
CheckBox1 = False
CheckBox2 = False
CheckBox3 = False
End If
End Sub

 

[NickH]

Hi Karissa,

If you use OptionButtons, rather than CheckBoxes, this behaviour will
be automatic.

Put OptionButtons within unique Frames to get them to behave as a
group.

Let me know if I've misunderstood you.

Br,
NickH

 

[JLGWhiz]

Are the checkboxes numbered 1 thru 80 on a sheet or are the on individual
form and numbered 1 thru 4 on each form? If they are on the forms, you could
just copy the code 19 times to the checkbox1 on the other forms.

As far as looping to write the code for the 1 thru 80, it could get pretty
hairy.

 

[Peter T]

Just to add -

As Nick suggests OptionButtons work like Radio-buttons, press one and all
others are off. With ActiveX OptionButtons (looks like you have aX
Checkboxes) you can have sets of mutually exclusive OB's by assigning the
GroupName property to suit.

In Design mode, click properties and change GroupName's to suit.

Here's some code to make a new set of 20 sets of 4 optionbuttons

Suggest do not run this code in the same project of the activesheet. Also do
not "break" or step into the code.

Sub AddOBs()
Dim ole As OLEObject
Dim i As Long, j As Long

For i = 1 To 20
For j = 1 To 4
Set ole = ActiveSheet.OLEObjects.Add("Forms.OptionButton.1")

With ole
.Left = (j - 1) * 150 + 10
.Top = (i - 1) * 50 + 10
.Name = "OB_" & i & "_" & j
.Object.GroupName = "Set_" & i
.Object.Caption = "Set " & i & " Choice " & j
End With
Next
Next

End Sub


Regards,
Peter T





If you go with Nick's suggestion (and probably you should), you can

 

[Karissa]

Thanks to all - The option boxes worked the tip on the Group name helped. I
wasn't sure how to make just the four in a row link together without using
the group box (that would have been messy).