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

 

[Tojomv]

Do you have a separate command button for each checkbox?

Yes, Cmd110 etc.

 

[Tojomv]

Hi,

Yes each checkbox has a corresponding Command Button which is clicked each time that checkbox is ticked. Cmd110, Cmd111 etc.

 

[Fluff]

Add the line in blue

Private Sub Cmd110_Click()
   Dim Rw As Long
   [COLOR=#0000ff]MsgBox Evaluate("counta(C40:AG11)")[/COLOR]
   If Evaluate("counta(C40:AG11)") >= 200 Then
      CmdBiModal.Visible = OptBGreen.Value
      CmdNormal.Visible = OptBYellow.Value
      CmdSkewed.Visible = OptBlue.Value
      Exit Sub
   End If
   Rw = Evaluate("counta(C40:C11)")
   If Me.ChkB110 Then
      If Rw < 30 Then
         Range("C" & 40 - Rw).Value = "X"
         Me.ChkB110 = False
      Else
         MsgBox "You have already entered 30 X's"
         Me.ChkB110 = False
      End If
   End If
End Sub

Does the msgbox show how many Xs you have every time you click the button?

 

[Tojomv]

Hi, Thanks for getting back to me.

Yes it is counting however the first count is 0. Having run through the full 200 it doesn't show the command button. BiModal, Normal or Skewed.

 

[Fluff]

Try this

   If Evaluate("counta(C40:AG11)") >= 200 Then
     [COLOR=#0000ff] MsgBox "Full"[/COLOR]
      cmdbimodal.Visible = OptBGreen.Value

If the msgbox appears, then I'd suggest that you check the spelling of the optionbuttons & commandbuttons.

 

[Tojomv]

Checked through code and all looks fine, all of the check boxes on the form are labelled as in code also, as are the command buttons cmdBiModal etc.
I have reverted to putting the buttons visible on selection of the relevant option button.

Thank you very much for your time spent on this, really appreciated.

 

[Tojomv]

didn't see this.
will give it a go and let you know.

 

[Tojomv]

Hi,

Thank you for all your efforts, i have now got this to work. Your code was spot on it was something to do with my code. However I have now come across another issue. I need the value to be flexible rather than fixed, the only way i could see it working like that is by using a variable RodsCount which needs user input via inputbox to enter the number of rods being measured, and using if statements to show message if input is too low (lower than 180) or too high (if over 200).

Original code but with new variable RodsCount

Dim RodsCount As Long


Private Sub Cmd110_Click()
    
Dim Rw As Long
   If Evaluate("counta(C40:AG11)") = RodsCount Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True = RodsCount Then
        CmdNormal.Visible = True
      ElseIf OptBBlue.Value = True Then
        CmdSkewed.Visible = True
      End If
      Exit Sub
   End If
   Rw = Evaluate("counta(C40:C11)")
   If Me.ChkB110 Then
      If Rw < 30 Then
         Range("C" & 40 - Rw).Value = "X"
         Me.ChkB110 = False
      Else
         MsgBox "You have already entered 30 X's"
         Me.ChkB110 = False
      End If
   End If
End Sub

Code that is placed under click of each Option Button (Green, Yellow, Blue - example for yellow OptionButton)

Private Sub OptBYellow_Click()
    
     If OptBYellow.Value = True Then
         ThisWorkbook.Sheets("Yellow").Select
         RodsCount = InputBox("Please Enter the total number of Yellow rods, value must be between 180 & 200")
            If RodsCount < 180 Or RodsCount > 200 Then
                MsgBox ("You have entered an invalid quantity!: " & RodsCount)
                RodsCount = InputBox("Please Enter a value between 180 & 200")
            End If
      End If


End Sub

I have 2 issues with this:

1) If user for some reason puts a low value i.e. 120 it brings up the message box as intended and requests data again, if they put a high number in next it just accepts it despite being outside parameters i.e. over 200, and same in reverse.

2) it needs to be between 180 and 200 without user input but the code below stops user input as soon as 180 is reached.

Dim RodsCount As Long


Private Sub Cmd110_Click()
    
Dim Rw As Long
   If Evaluate("counta(C40:AG11)") >= 180 And <= 200 Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True = RodsCount Then
        CmdNormal.Visible = True
      ElseIf OptBBlue.Value = True Then
        CmdSkewed.Visible = True
      End If
      Exit Sub
   End If
   Rw = Evaluate("counta(C40:C11)")
   If Me.ChkB110 Then
      If Rw < 30 Then
         Range("C" & 40 - Rw).Value = "X"
         Me.ChkB110 = False
      Else
         MsgBox "You have already entered 30 X's"
         Me.ChkB110 = False
      End If
   End If
End Sub

Any advise appreciated

 

[Fluff]

Try

Private Sub OptBYellow_Click()
   Dim flag As Boolean
   If OptBYellow.Value = True Then
      Do Until flag
         RodsCount = InputBox("Please Enter the total number of Yellow rods, value must be between 180 & 200")
         If RodsCount < 180 Or RodsCount > 200 Then
            MsgBox ("You have entered an invalid quantity!: " & RodsCount)
         Else
            flag = True
         End If
      Loop
      ThisWorkbook.Sheets("Yellow").Select
   End If
End Sub

and

Private Sub Cmd110_Click()
    
Dim Rw As Long
   If Evaluate("counta(C40:AG11)") =RodCount Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True

 

[Tojomv]

Thank you again.

I played around a bit more as didn't want InputBox if could help it, but needed user to be able to input more than 180 inputs but still reveal Cmd Button at 180. Came up with this in the end.

  Dim Rw As Long
  Dim RodsCount As Integer
  Dim i As Integer
    
    RodsCount = 200
    i = 180
    
   If Evaluate("counta(C40:AG11)") = i Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True Then
        CmdNormal.Visible = True
      ElseIf OptBBlue.Value = True Then
        CmdSkewed.Visible = True
      End If
      If Evaluate("counta(C40:AG11)") = RodsCount Then
      Exit Sub
      End If
   End If
   Rw = Evaluate("counta(C40:C11)")
   If Me.ChkB110 Then
      If Rw < 30 Then
         Range("C" & 40 - Rw).Value = "X"
         Me.ChkB110 = False
      Else
         MsgBox "You have already entered Max number of measurements for this rod size!"
         Me.ChkB110 = False
      End If
   End If
End Sub

I am not too worried that this permits more than 200 x's into the spreadsheet as they only have a maximum of 200 rods for each colour (though a bit stumped as to why it doesnt exit sub when rods count is reached).

Thank you again for your help, it has certainly made me understand a bit more of VBA.