Testing that all checkboxes are ticked before action

[Katherine]

I've blundered my way through creating a userform but have come unstuck on one particular problem. I have a series of checkboxes, and i want the command button to only become enabled when all the checkboxes are ticked. I can get this to work for one checkbox by setting the command button's enabled property to false and then usin

if chkOne=true the
cmdSubmit.enabled=tru

but i need to test if *all* the checkboxes are ticked, not just the first one. I've tried ifs and elses and ands but i'm getting nowhere fast.

What am i doing wrong? Any and all suggestions appreciated.

 

[Jonathan West]

I've blundered my way through creating a userform but have come unstuck on

one particular problem. I have a series of checkboxes, and i want the
command button to only become enabled when all the checkboxes are ticked. I
can get this to work for one checkbox by setting the command button's
enabled property to false and then using

if chkOne=true then
cmdSubmit.enabled=true

but i need to test if *all* the checkboxes are ticked, not just the first

one. I've tried ifs and elses and ands but i'm getting nowhere fast.

What am i doing wrong? Any and all suggestions appreciated.

Hi Katherine,

You can go through the Controls collection of the userform, Put this
function into your userForm

Private Function AllChecked() as Boolean
Dim oControl as Control
For Each oControl in Me.Controls
If TypeOf oControl is MSForms.Checkbox Then
If Not oControl.Value Then
AllChecked = False
Exit Function
End If
End If
Next oControl
AllChecked = True
End Function

What this does is take a look at each control in teurn. it checks if it is a
checkbox. If it is, it checks the value. If any checkbox is not checked,
then the routine exits immediately with a value of False. If all the
checkboxes are checked, the routine eventually exits with value of True.

Then all you need do is call the function whenever you need to see whetehr
all the checkboxes have been checked.

 

[Jezebel]

Write a macro that checks ALL the checkboxes, and run that on exit from ALL
the checkbox controls: that way it doesn't matter what order the checkboxes
are ticked in:

cmdSubmit.Enabled = (chkOne and chkTwo and chkThree)

I've blundered my way through creating a userform but have come unstuck on

one particular problem. I have a series of checkboxes, and i want the
command button to only become enabled when all the checkboxes are ticked. I
can get this to work for one checkbox by setting the command button's
enabled property to false and then using

if chkOne=true then
cmdSubmit.enabled=true

but i need to test if *all* the checkboxes are ticked, not just the first

one. I've tried ifs and elses and ands but i'm getting nowhere fast.

 

[Katherine]

Fantastic. Thanks, that worked like a charm.

I've had a go at altering that function so that it will test whether the user has entered information into two textboxes but its kicking up an error (object does not support this property or method) so i assumei'm using the wrong thing for the wrong job. Basically i have two text fields that are mandatory, so i want to ensure that they have been completed before the submit button is enabled.

Any clues? If you can help me out on this one Jonathan you're welcome to my firstborn

 

[Jonathan West]

Fantastic. Thanks, that worked like a charm.

I've had a go at altering that function so that it will test whether the

user has entered information into two textboxes but its kicking up an error
(object does not support this property or method) so i assumei'm using the
wrong thing for the wrong job. Basically i have two text fields that are
mandatory, so i want to ensure that they have been completed before the
submit button is enabled.

Are these the only two textboxes on the UserForm?

If so, then you can alter the code like this

Private Function TextBoxesFilled() as Boolean
Dim oControl as Control
For Each oControl in Me.Controls
If TypeOf oControl is MSForms.TextBox Then
If Len(oControl.Text) = 0 Then
TextBoxesFilled = False
Exit Function
End If
End If
Next oControl
TextBoxesFilled = True
End Function

If only certain textboxes are mandatory, then you need to have a way of
identifying them. The approach I usually take for this is to put some text
(such as "mandatory") into the Tag property of the Textbox. The tag property
doesn't display on the form and is very useful for this kind of thing. With
the Tag property set for the mandatory textboxes, the code would now need to
be as follows.

Private Function TextBoxesFilled() as Boolean
Dim oControl as Control
For Each oControl in Me.Controls
If TypeOf oControl is MSForms.TextBox Then
If oControl.Tag = "mandatory" Then
If Len(oControl.Text) = 0 Then
TextBoxesFilled = False
Exit Function
End If
End If
End If
Next oControl
TextBoxesFilled = True
End Function


If you change your mind about which textboxes are mandatory, you don't need
to bother altering the code, you simply add or remove "mandatory" from the
tag property as appropriate.

Any clues? If you can help me out on this one Jonathan you're welcome to

my firstborn