Solved: Yes/No Checkboxes Complete Before E-mail?

[Derek]

That is a rather confusing title I think so I apologise and will explain. I wish to ensure that, prior to e-mailing a form to a distribution list, that all questions have been answered by clicking either a Yes or No Checkbox. If details are required, I also want to ensure that those details are given. Can anyone start me on my way with this with some ideas or code? I have set up a simple IF formula to return TRUE if neither relevant checkbox is ticked. Am I on the right road with this or is there a better way? I would like an error message to pop up if the answer for any of these options is TRUE that says something like "Please go back and fully complete the form prior to sending"

Any thoughts?

[Bob Phillips]

That is a rather confusing title I think so I apologise and will explain. I wish to ensure that, prior to e-mailing a form to a distribution list, that all questions have been answered by clicking either a Yes or No Checkbox. If details are required, I also want to ensure that those details are given. Can anyone start me on my way with this with some ideas or code? I have set up a simple IF formula to return TRUE if neither relevant checkbox is ticked. Am I on the right road with this or is there a better way? I would like an error message to pop up if the answer for any of these options is TRUE that says something like "Please go back and fully complete the form prior to sending"

Any thoughts?

Derek,

What sort of checkboxes do you have. How are they set up (linked?)

What technology do you use to send the email.

What have you already got?

[Derek]

I have tried to attach a sample file which will answer all your questions but it seems I am only aloowed one file at a time, and I have a file attached to a thread called "ComboBox query". The checkboxes are linked to cells, so that I can see a text Yes or No. All our machines use Outlook and I already have the e-mail working fine. I just need the warning/error message prior to creation of the e-mail.

As I said previously, I have used a simple IF formula to check if both cells say nothing, returning a true/false answer. My thoughts were that there could be some code to check the text in these cells and if any say TRUE then an error message could pop up stating that the form is incomplete, and that the user must go back and complete.

Any help is most appreciated

[Bob Phillips]

I have tried to attach a sample file which will answer all your questions but it seems I am only aloowed one file at a time, and I have a file attached to a thread called "ComboBox query". The checkboxes are linked to cells, so that I can see a text Yes or No. All our machines use Outlook and I already have the e-mail working fine. I just need the warning/error message prior to creation of the e-mail.

As I said previously, I have used a simple IF formula to check if both cells say nothing, returning a true/false answer. My thoughts were that there could be some code to check the text in these cells and if any say TRUE then an error message could pop up stating that the form is incomplete, and that the user must go back and complete.

Any help is most appreciated

I would check the checkboxes directly in the code

[vba]
If Not ActiveSheet.CheckBoxes("Check Box 1") = 1 Or _
Not ActiveSheet.CheckBoxes("Check Box 2") = 1 Then
MsgBox "incomplete"
Else
'do the Outlook stuff
End If
[/vba]

[Derek]

When I use that code I get an error message which says:

Run-time error '1004'
Unable to get the checkboxes property of the worksheet class

I have played with the Check Box 1 name to CheckBox1 etc and changed the value from 1 to 0 but still get the same error.

Thoughts?

[Derek]

Just a quick post to remind you all that I need some help.

[Ken Puls]

I have tried to attach a sample file which will answer all your questions but it seems I am only aloowed one file at a time, and I have a file attached to a thread called "ComboBox query".

Hi Derek,

I don't think that should matter. As long as you try to attach a zip file, it should work. I know that I have many concurrent files uploaded...

[mdmackillop]

Try the following for the checkbox check
[VBA]
Sub AllFilled()
For Each X In ActiveSheet.OLEObjects
If X.ProgId = "Forms.CheckBox.1" Then
If IsNull(X.Object.Value) Then
MsgBox "Please fill all checkboxes"
Exit Sub
End If
End If
Next
End Sub

[/VBA]

[Killian]

I think the problem here is that not all the check boxes need to be checked... if fact half of them should be (provided they are all Yes/No)
That being the case, you could just count them all, and count the number of checked ones to confirm all questions have been answered[VBA]Dim lngChkBoxCount As Long
Dim lngChkBoxTickedCount As Long
Dim ctrl As Object

For Each ctrl In ActiveSheet.OLEObjects
If TypeName(ctrl.Object) = "CheckBox" Then
lngChkBoxCount = lngChkBoxCount + 1
If ctrl.Object.Value Then
lngChkBoxTickedCount = lngChkBoxTickedCount + 1
End If
End If
Next ctrl

If Not lngChkBoxTickedCount = lngChkBoxCount / 2 Then
MsgBox "Please complete all questions"
Else
'send workbook
End If[/VBA]One issue here is that some bright spark could check yes and no in one question and nothing in another and the test would still pass.
This is why you should be using OptionButtons and setting the GroupName property the same for each Yes/No pair so that for each question, only one answer can be given.
You could still use this code, just change the TypeName test from "CheckBox" to "OptionButton".

[Derek]

Thanks to everyone who has helped with this little problem. Using your feedback AND my original idea, I got this to work rather nicely.

What I did was to craete a simple little IF formula/function to return TRUE if neither relevant boxes were ticked. then simple VBA code saying if TRUE returned "Go Back and Fix!" message popped up.

Thank you all for your input. It once again was invaluable in coming up with the solution.