count number checkboxes

[Tojomv]

Hi,

I have checked through the forum and wasn't able to find exactly what I need in order to perform the task required.

I have a userform (UserForm1) which has checkboxes labelled 110 through to 140. Each time a checkbox is selected it places an X in the grid on an excel spreadsheet, so for instance if 110 was selected it would place the first X in range c40 if it was selected again it would place the next X in range C39 (this I have coded using If Statements see portion of code below.

Range("C40").Activate
    
    CBox = ChkB110


    If CBox = True And Range("C40").Value = "" Then
        Range("C40").Value = "X"
        ChkB110.Value = False
    ElseIf Range("C40").Value = "X" And Range("C39").Value = "" Then
        Range("C39").Value = "X"
        ChkB110.Value = False
    ElseIf Range("C39").Value = "X" And Range("C38").Value = "" Then
        Range("C38").Value = "X"

I have done this for every checkbox 110 through to 140 and for maximum of 30 X's for each checkbox. (lot of repeat coding as couldn't work out how to create the necessary function for this.

Now I need to count the number of checkboxes ticked so when the count reaches 200 it will reveal a command button based on which option button was selected at the beginning (Green, Yellow, or Blue). I have used a case statement to try and determine which option was selected and which button to reveal, however on testing it does not work, it will count first instance of the checkbox and assign 1 to i but then does nothing else.

I have placed this piece of code in the UserForm1 initialize.

  Dim contr As Control
  Dim i As Integer
    
    i = 0
    
    For i = 1 To 200
    
    For Each contr In UserForm1.Controls
    If TypeOf contr Is MSForms.CheckBox Then
    If UserForm1.Controls(contr.Name).Value = True Then i = i + 1
    End If
    Next
    
    Select Case RodsCount
    
        Case BiModal
        If i = 200 And OptBGreen.Value = True Then
        CmdBiModal.Visible = True
        End If
        
        Case Normal
        If i = 200 And OptBYellow.Value = True Then
        CmdNormal.Visible = True
        End If
        
        Case Skewed
        If i = 200 And OptBlue.Value = True Then
        CmdSkewed.Visible = True
        End If
        
    End Select
    
Next

I try hard to work the solution myself however on this I am stumped. Any help / advice would be very much appreciated.

TIA
Mark

 

[Fluff]

Glad we could help & thanks for the feedback