Restricting command button action in a user form

**coach.32** · 06-03-2013, 12:21 AM

In my Userform I would like to stop a command button from being pressed until a minimum of 5 checkboxes out of 10 have been selected.
I have a total of how many checkboxes have been selected. How do I write the code on the command button that if Label X (which has the total of the checkboxes) is greater than 4 then run the Comman Button to exit Excel.
Thank you for your help.

**:) Sixthsense :)** · 06-03-2013, 12:38 AM

Use a textbox instead of label and try to code something like this...

Private Sub CheckBox1_Click()
    abc = IIf(CheckBox1.Value = True, 1, 0) + IIf(CheckBox2.Value = True, 1, 0) + IIf(CheckBox3.Value = True, 1, 0)
    TextBox1.Value = abc
End Sub
Private Sub CheckBox2_Click()
    abc = IIf(CheckBox1.Value = True, 1, 0) + IIf(CheckBox2.Value = True, 1, 0) + IIf(CheckBox3.Value = True, 1, 0)
    TextBox1.Value = abc
End Sub
Private Sub CheckBox3_Click()
    abc = IIf(CheckBox1.Value = True, 1, 0) + IIf(CheckBox2.Value = True, 1, 0) + IIf(CheckBox3.Value = True, 1, 0)
    TextBox1.Value = abc
End Sub
Private Sub TextBox1_Change()
    If TextBox1.Value > 2 Then
        CommandButton1.Enabled = True
    Else
        CommandButton1.Enabled = False
    End If
End Sub

**JapanDave** · 06-03-2013, 12:40 AM

You could do it this way.

Private Sub CheckBox1_Click()
   Dim cBox As MSForms.Control
   Dim y As Long
   
      y = 0
    For Each cBox In Me.Controls
        If TypeName(cBox) = "CheckBox" Then
          If cBox = True Then
           y = y + 1
             If y > 4 Then
               Me.CommandButton1.Enabled = 1
             End If
          End If
        End If
    Next cBox
End Sub

Private Sub UserForm_Initialize()

  Me.CommandButton1.Enabled = 0
  
End Sub