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.
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.
I try hard to work the solution myself however on this I am stumped. Any help / advice would be very much appreciated.
TIA
Mark
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