count number checkboxes

Tojomv

New Member
Joined
Dec 31, 2014
Messages
18
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.

Code:
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.

Code:
  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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You might need to expand on your explanation. There are a couple of things I don't understand.

I have a userform (UserForm1) which has checkboxes labelled 110 through to 140.

That would suggest there are 30 checkboxes on the form.

Now I need to count the number of checkboxes ticked so when the count reaches 200

If there are only 30 checkboxes on the form, I'm not sure how counting the checkbox true/false state is going to get you what you want. If each time a checkbox is ticked, it puts an "X" in a different cell, shouldn't you be counting the number of cells with populated with "X" instead of checkbox values?

Also, while I don't yet fully understand your intent, you might want to look at your looping. You have an inner For..Each loop nested with an outer For i = 1 to 200 loop. Then within the (inner) For..Each loop you are messing around (i = i + 1) with the looping variable i for the outer loop. Usually nothing good can come from that.
 
Last edited:
Upvote 0
How about something like this for each CheckBox
Code:
Private Sub ChkB110_Click()
   Dim Rw As Long
   If Evaluate("counta(C[COLOR=#ff0000]40:E11[/COLOR])") = 200 Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True Then
        CmdNormal.Visible = True
      ElseIf OptBlue.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
Change the range in red to cover the ranges used by your Chkboxes
 
Upvote 0
HI,

The grid on the spreadsheet is a 30 cell by 30 cell grid. The maximum number of x's in each range will be a count of 30 (110, 111 etc). The 110 to 140 represent set measurements in mm.
I could set the count for the number of x's which will give me the same result as counting the checkboxes. When the count reaches 200 then it reveals the correct command button based on the initial selection of radio button through the case statement.

The looping structure is where I was getting issues with ( I am still trying to learn vba as I go along). I set i as the variable to hold the count, set it's initial value to 0 then set the range of i from 1 to 200 as that will be the required count to initiate the reveal of the command button.

Sorry if it is still not clear.

Mark
 
Upvote 0
How about something like this for each CheckBox
Code:
Private Sub ChkB110_Click()
   Dim Rw As Long
   If Evaluate("counta([COLOR=#ff0000]C40:E11[/COLOR])") = 200 Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True Then
        CmdNormal.Visible = True
      ElseIf OptBlue.Value = True Then
        CmdSkewed.Visible = True
      End If
      Exit Sub
   End If
   Rw = Evaluate("counta([B][COLOR="#0000FF"]C40:C11[/COLOR][/B])")
   If Me.ChkB110 Then
      If Rw < 30 Then
         Range("[B][COLOR="#0000FF"]C[/COLOR][/B]" & [B][COLOR="#0000FF"]40[/COLOR][/B] - Rw).Value = "X"
         Me.ChkB110 = False
      Else
         MsgBox "You have already entered 30 X's"
         Me.ChkB110 = False
      End If
   End If
End Sub
Change the range in red to cover the ranges used by your Chkboxes
Besides changing what you highlighted in red (I correct the colored range to include the "C" which you coloring omitted), would the user have to simultaneously have to change what I highlighted in blue?

Also, I think this part of your code...
Code:
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True Then
        CmdNormal.Visible = True
      ElseIf OptBlue.Value = True Then
        CmdSkewed.Visible = True
      End If
could be simplified to this...
Code:
      CmdBiModal.Visible = OptBGreen.Value
      CmdNormal.Visible = OptBYellow.Value
      CmdSkewed.Visible = OptBlue.Value
 
Last edited:
Upvote 0
Besides changing what you highlighted in red (I correct the colored range to include the "C" which you coloring omitted), would the user have to simultaneously have to change what I highlighted in blue?
Good point Rick, yes that will need to be changed for each checkbox.
And I like your simplified code for the buttons.
 
Upvote 0
Code:
Private Sub UserForm_Activate()
    Dim WS As Worksheet
    Dim RangeOfCells As Range
    Dim R As Range
    Dim XCount As Long
    Dim FirstAddr As String

    Set WS = ThisWorkbook.Worksheets("Sheet1")    'or whatever your worksheet is named
    Set RangeOfCells = WS.Range("C11:E40")    'your grid of 30 x 30 cells

    'Count the number of cells in the grid containing "X"
    XCount = 0
    Set R = RangeOfCells.Find(What:="X", Lookat:=xlWhole)
    If Not R Is Nothing Then
        FirstAddr = R.Address
    End If

    Do While Not R Is Nothing
        XCount = XCount + 1
        Set R = RangeOfCells.Find(What:="X", After:=R, Lookat:=xlWhole, SearchDirection:=xlNext)
        If R.Address = FirstAddr Or XCount = 200 Then
            Exit Do
        End If
    Loop

    'Change button visibilty if XCount = 200
    If XCount = 200 Then
        Select Case RodsCount
        Case BiModal
            CmdBiModal.Visible = OptBGreen.Value
        Case Normal
            CmdNormal.Visible = OptBYellow.Value
        Case Skewed
            CmdSkewed.Visible = OptBlue.Value
        End Select
    End If
End Sub
 
Upvote 0
Thank you for this, sorry for the delay in replying.

I have added the code however been spending time investigating why the count is not working. Initially it was counting and the button was only showing when the count reached 225 not 200, now it is not counting at all.

All I have done is remove my blocks of code that were shown in initial post. As Rick mentioned below the ranges he highlighted in Blue did need to be changed to reflect accordingly which I understood prior and had done so already.

The only thing I have changed apart from the ranges is the fact that once the checkbox is ticked a command button is clicked to enter the data on the spreadsheet.

Code:
Private Sub Cmd110_Click()


   Dim Rw As Long
   If Evaluate("counta(C40:AG11)") = 200 Then
      If OptBGreen.Value = True Then
         CmdBiModal.Visible = True
      ElseIf OptBYellow.Value = True Then
        CmdNormal.Visible = True
      ElseIf OptBlue.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 "Maximum number allowed for this measurement, please re-check!"
         Me.ChkB110 = False
      End If
   End If

Regards
Mark
 
Upvote 0
Thank you Rick, I have amended the else if statement to your shortened code.

Code:
Private Sub Cmd110_Click()


   Dim Rw As Long
   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
 
Upvote 0
Do you have a separate command button for each checkbox?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top