Check Boxes question

[LEU]

I have the following macro (First set) that when you check a box it puts a
number in a textbox and then totals all the textboxes. It works if I check a
box, but if I make a mistake and uncheck that box and check a new box it
doesn't correct the number in the textbox. I have 10 sets of 5 checkboxes
that I add up.

Private Sub CheckBox1_Click()
For i = 2 To 5
If CheckBox1.Value = True Then
Me.Controls("CheckBox" & i) = False
Me.Controls("CheckBox" & i).Enabled = False
Else
Me.Controls("CheckBox" & i).Enabled = True
End If
Next i

If CheckBox1 = True Then
TNbr1 = "1"
TextBox100 = TNbr1
ElseIf CheckBox2 = True Then
TNbr1 = "2"
TextBox100 = TNbr1
ElseIf CheckBox3 = True Then
TNbr1 = "3"
TextBox100 = TNbr1
ElseIf CheckBox4 = True Then
TNbr1 = "4"
TextBox100 = TNbr1
ElseIf CheckBox5 = True Then
TNbr1 = "5"
TextBox100 = TNbr1
End If
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

 

[Jean-Guy Marcil]

LEU was telling us:
LEU nous racontait que :

I have the following macro (First set) that when you check a box it
puts a number in a textbox and then totals all the textboxes. It
works if I check a box, but if I make a mistake and uncheck that box
and check a new box it doesn't correct the number in the textbox. I
have 10 sets of 5 checkboxes that I add up.

It seems that if you can write code to add a value to a textbox based on the
value from a checkbox, you can also write code to remove the value from the
said textbox based on a different value obtained from the said checkbox.

I presume the problem lies elsewhere, but from what you wrote I cannot see
what the problem is.

 

[LEU]

Marcil,

This is what I did to make it work, but I hope there is a better way because
I have 50 CheckBoxes.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
CheckBox5.Enabled = False
Else
CheckBox2.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
CheckBox5.Enabled = True
End If
TNbr1 = "1"
If CheckBox1.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
CheckBox1.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
CheckBox5.Enabled = False
Else
CheckBox1.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
CheckBox5.Enabled = True
End If
TNbr1 = "2"
If CheckBox2.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

Private Sub CheckBox3_Click()
If CheckBox3.Value = True Then
CheckBox1.Enabled = False
CheckBox2.Enabled = False
CheckBox4.Enabled = False
CheckBox5.Enabled = False
Else
CheckBox1.Enabled = True
CheckBox2.Enabled = True
CheckBox4.Enabled = True
CheckBox5.Enabled = True
End If
TNbr1 = "3"
If CheckBox3.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

Private Sub CheckBox4_Click()
If CheckBox4.Value = True Then
CheckBox1.Enabled = False
CheckBox2.Enabled = False
CheckBox3.Enabled = False
CheckBox5.Enabled = False
Else
CheckBox1.Enabled = True
CheckBox2.Enabled = True
CheckBox3.Enabled = True
CheckBox5.Enabled = True
End If
TNbr1 = "4"
If CheckBox4.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

Private Sub CheckBox5_Click()
If CheckBox5.Value = True Then
CheckBox1.Enabled = False
CheckBox2.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
Else
CheckBox1.Enabled = True
CheckBox2.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
End If
TNbr1 = "5"
If CheckBox5.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

 

[Jean-Guy Marcil]

LEU was telling us:
LEU nous racontait que :

Marcil,

This is what I did to make it work, but I hope there is a better way
because I have 50 CheckBoxes.

Are you saying that the code below would actually be enabling/disabling 50
checkboxes?

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
CheckBox5.Enabled = False
Else
CheckBox2.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
CheckBox5.Enabled = True
End If
TNbr1 = "1"
If CheckBox1.Value = False Then TNbr1 = "0"
TextBox100 = TNbr1
TextBox110 = (TNbr1 + TNbr2 + TNbr3 + TNbr4 + TNbr5 + _
TNbr6 + TNbr7 + TNbr8 + TNbr9 + TNbr10)
End Sub

Why are you enabling/disabling textboxes like that?

Also, it seems that whatever the checkbox, the result is the same.
In other words, a string variable is set to "0" or "1" and then that string
variable is used in a mathematical operation?

 

[LEU]

Marcil,

I have 10 rows with 5 check boxes in each row. So if I am in row 1 and I
check the first box my value is 1. I can only check 1 box per row. So i
disable check boxes 2 thru 5. If I check box 3 then my value will be 3 and I
disable boxes 1, 2, 4 and 5. If I check a box and then uncheck that box, TNBR
(interger) can't be blank so I make it 0.

 

[Jean-Guy Marcil]

LEU was telling us:
LEU nous racontait que :

Marcil,

I have 10 rows with 5 check boxes in each row. So if I am in row 1
and I check the first box my value is 1. I can only check 1 box per
row. So i disable check boxes 2 thru 5. If I check box 3 then my
value will be 3 and I disable boxes 1, 2, 4 and 5. If I check a box
and then uncheck that box, TNBR (interger) can't be blank so I make
it 0.

So, you should be using OptionButton and group names, this way you do not
need to enable or disable anything.

I am not quite sure what you are doing with TextBox100 and TextBox110, but I
have cobbled together some code to give you another idea on how to approach
this.

On a UserForm, place two rows of 3 OptionButton, named OptionButton1 to
OptionButton6.

Select the first three on the first row (OptionButton1 to OptionButton3) and
in the properties pane, assign the value "Group1" to those controls
GroupName property. Do the same with the other three OptionButton, but
assign the value "Group2"
Now, take the first control of each group (OptionButton1 and OptionButton4)
and assign the value "1" to those control Tag property. Repeat for the
second and third controls, assigning 2 and 3 respectively.

Finally, paste this code in the UseForm code pane:


Option Explicit

Dim TNbr1 As Long
Dim TNbr2 As Long

Private Sub OptionButton1_Click()

SetValueGroup Me.OptionButton1.GroupName

End Sub

Private Sub OptionButton2_Click()

SetValueGroup Me.OptionButton2.GroupName

End Sub

Private Sub OptionButton3_Click()

SetValueGroup Me.OptionButton3.GroupName

End Sub

Private Sub OptionButton4_Click()

SetValueGroup Me.OptionButton4.GroupName

End Sub

Private Sub OptionButton5_Click()

SetValueGroup Me.OptionButton5.GroupName

End Sub

Private Sub OptionButton6_Click()

SetValueGroup Me.OptionButton6.GroupName

End Sub

Private Sub SetValueGroup(strGroup As String)

Dim ctrl As Control
Dim i As Long

With Me
For Each ctrl In .Controls
If TypeOf ctrl Is MSForms.OptionButton Then
Select Case ctrl.GroupName
Case "Group1"
If strGroup = "Group1" Then
If ctrl.Value Then
TNbr1 = CLng(ctrl.Tag)
Me.TextBox100 = CStr(TNbr1)
End If
End If
Case "Group2"
If strGroup = "Group2" Then
If ctrl.Value Then
TNbr2 = CLng(ctrl.Tag)
Me.TextBox100 = CStr(TNbr2)
End If
End If
End Select
End If
Next
End With

Me.TextBox110 = CStr(TNbr1 + TNbr2)

End Sub