Solved: Checking the value of mulitple checkboxes

[Aussiebear]

I have three checkboxes on a form (ckbRules, ckbFees & ckbIndemify), and I need all three to be true for the cmdsave button to be visible. How is this done?

Edit: As the form is being completed in tab order, the last three items of required information to be filled are the three checkboxes and unless the value of all three are true (checked) then you cannot save the data on the form. So do I write this as

If ckbRules.Value And ckbFees.Value And ckbIndemify.Value <> True Then
cmdSave.visible = False
Else
cmdSave.Visible = True
End If


or do I need to loop through each control named ckb*.Value

[Kenneth Hobs]

Yes, looping through the like-named prefix controls would be a good solution.

Here is a method where I used a control array. Depending on the number of controls, your looping method might or might not be a bit better.

Dim cbs(2) As MSForms.CheckBox

Private Sub ckbFees_Click()
cmdVisible
End Sub

Private Sub ckbIndemify_Click()
cmdVisible
End Sub

Private Sub ckbRules_Click()
cmdVisible
End Sub

Private Sub UserForm_Initialize()
cmdsave.Visible = False
Set cbs(0) = ckbFees
Set cbs(1) = ckbIndemify
Set cbs(2) = ckbRules
End Sub

Private Sub cmdVisible()
Dim i As Integer, tf As Integer
For i = 0 To 2
tf = tf + cbs(i)
Next i
If tf = -3 Then
cmdsave.Visible = True
Else
cmdsave.Visible = False
End If
End Sub

[mikerickson]

cmdSave.visible = ckbRules.Value And ckbFees.Value And ckbIndemify.Value

BTW, is "indemify" misspelt?

[Aussiebear]

@Kenneth, If just one of the checkbox values is false then the button cmdSave must remain invisible. It seems a lot of code....

@Mike, Yes I did make a spelling error. Good catch.

[Kenneth Hobs]

Variable names don't care whether they are misspelled or not. That is why I seldom ask for that type of clarification.

Lots of code should not be a problem. Here is the all control iteration method. The other method that someone like xld or Mike might pipe in with is the use of a Class.

Private Sub ckbFees_Click()
cmdVisible
End Sub

Private Sub ckbIndemify_Click()
cmdVisible
End Sub

Private Sub ckbRules_Click()
cmdVisible
End Sub

Private Sub UserForm_Initialize()
cmdsave.Visible = False
End Sub

Private Sub cmdVisible()
Dim c As Control, tf As Integer
For Each c In Controls
If Left(c.Name, 3) = "ckb" Then tf = tf + c.Value
Next c
If tf = -3 Then
cmdsave.Visible = True
Else
cmdsave.Visible = False
End If
End Sub

[Paul_Hossler]

'Nuther way


Option Explicit


Private Sub ckbRules_Change()
cmdSave.Visible = ckbRules.Value And ckbFees.Value And ckbIndeminify.Value
End Sub


Private Sub ckbFees_Change()
cmdSave.Visible = ckbRules.Value And ckbFees.Value And ckbIndeminify.Value
End Sub


Private Sub ckbIndeminify_Change()
cmdSave.Visible = ckbRules.Value And ckbFees.Value And ckbIndeminify.Value
End Sub

Private Sub UserForm_Initialize()
Application.EnableEvents = False
cmdSave.Visible = False
ckbRules.Value = False
ckbFees.Value = False
ckbIndeminify.Value = False
Application.EnableEvents = True
End Sub



Paul

[Aussiebear]

Just to clarify a point. False is -1, unchecked is 0, and True is 1?

[Aussiebear]

@Kenneth Thank you for clearing up the code bit

@Paul Thank you also. I have been setting checkboxes to false in my form initialize already as well as when resetting the form after saving, but had failed to use the enable events code. Another good pick up.

[Kenneth Hobs]

True is -1 in VBA so 3 trues added are -3. False is 0. The checked property Value can be nothing (unitialized), True, or False. The boolean method of True And True And True equals True is a decent method..

Relying on the value of True or False as a static mathematical value is probably not a good practice. Some programming languages will vary from other languages on the actual value.
e.g.
VBA, VBS and VB.NET: True = -1, False = 0
R and PerfectScript: True = 1, False = 0 (PerfectScript is the programming language for Corel's WordPerfect)

Application.EnableEvents does not affect userform events.

I like to use the Immediate window in VBA to see values. e.g.
?True+0
Adding zero forces a type conversion.

[mikerickson]

When used arithmetically in worksheet formulas TRUE and FALSE take the values of 1 and 0.
When used arithmetically in VBA, True and Valse take values of -1 and 0.

[Aussiebear]

Talk about a high speed handbrake turn..... worksheet 1, vba -1