Sleeper: Enable Option Button only if more than 2 checkboxes are checked

[Djblois]

Is there a way I can have it enable a particular option button on a user form if the User checks more than one checkbox. And then if he unchecks it down to one he it will disable?

[Djblois]

I see from another thread how I can set the option button to disable but how do I set an if more than one checkbox in a collection?

[Bob Phillips]

Put this code in the procedure

Dim ctl As Control
Dim cCBs As Long
For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            If ctl.Value Then
                cCBs = cCBs + 1
            End If
        End If
    Next ctl
OptionButton1.Enabled = cCBs > 1

and call the procedure from all checkbox click routines.

[Djblois]

Where do I put the name of the option box? Where it says me should I put the name of the collection? Also would this disable the Option box again if the number of checks go down to 1?

[Bob Phillips]

OptionButton1.

No, that refers to the userform container.

Yes.

[Djblois]

 
Sub EnableOption()
Dim ctl As Control
Dim cCBs As Long
For Each ctl In DataView.Controls
    If TypeName(ctl) = "CheckBox" Then
        If ctl.Value Then
            cCBs = cCBs + 1
        End If
    End If
Next ctl
Column.Enabled = cCBs > 1
End Sub

It keeps telling me object required on the line:

For Each ctl In DataView.Controls

I tried the using the frame name (Dataview) and also the group name under properties for the checkboxes.

[Bob Phillips]

What is DataView? You said you were suing a Userform, so use Me.

[Djblois]

Whenever I use Me it says invalid use of me command

[Bob Phillips]

So is it really a userform?

[Djblois]

Maybe I am using the wrong terminology. I created a custom form in VBA.

[Bob Phillips]

You did Insert>Userform in the VBIDE?

[Djblois]

Yes

[Bob Phillips]

Can you post the workbook?

[Djblois]

ok