Userform CheckBox question.

[dbmathis]

Hi all,

I looked through the forums and didn't seem to fin anything like what I am trying to do.

I have 21 CheckBoxes. Named CheckBox1 - CheckBox21..

I need a macro the will do the following:

When any of the CheckBoxes within CheckBox1 - CheckBox21 is clicked / checked then all other checkboxes are greyed out / disabled but the one that was checked?

The what code can I use on my commandbutton to enable them all back at a later time?

Thanks.

 

[Norie]

When do you want this to occur?

Whenever a checkbox is clicked?

If you do then what you probably need is a class module.

I'll try and rustle something up and post back.

By the way why not just use option buttons?

 

[Norie]

Right this is what I came up with.

When you click a checkbox then it disables all the rest.

When you click it again it re-enables all the checkboxes.

In a Class module called CCheckBoxes put this code.

Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()
Dim ctl As MSForms.Control

    For Each ctl In UserForm1.Controls
        If CheckGroup.Value Then
            ctl.Enabled = ctl.Caption = CheckGroup.Caption
        Else
            ctl.Enabled = True
        End If
    Next ctl
End Sub

In the userform module put this code.

Option Explicit

Dim CheckBoxes() As New CCheckBoxes

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            I = I + 1
            ReDim Preserve CheckBoxes(1 To I)
            Set CheckBoxes(I).CheckGroup = ctl
        End If
    Next
End Sub

 

[dbmathis]

What is the difference, other than apperence, between the optionbox and the check box?

 

[Norie]

You can only pick one option button out of a group.

To group option buttons together use the GroupName property, which can easily be set by selecting all the option buttons for the group.

 

[dbmathis]

Ok, makes sense. Thanks Norie!