CheckBox Verify in UserForm

[rockwellsba]

Hi. I have a few CheckBoxes and lots of TextBoxes in my UserForm. Upon user click of the CommandButton [OK], the routine checks the form so that all fields are filled-in. What I don't know how to do is to have the CheckBox be verified (verify that the user has put a check in the checkbox). Any assistance is very appreciated.

Here's my code:
Private Sub CommandButton1_Click()
'' Validate all TextBoxes
If TextBox1.Text = "" Then
MsgBox "Name Of Applicant field is not complete"
TextBox1.SetFocus
Exit Sub
End If
If TextBox2.Text = "" Then
MsgBox "Contact Name field is not complete"
TextBox2.SetFocus
Exit Sub
End If
If TextBox3.Text = "" Then
MsgBox "Applicant Phone field is not complete"
TextBox3.SetFocus
Exit Sub
End If

If TextBox4.Text = "" Then
MsgBox "Applicant Fax field is not complete"
TextBox4.SetFocus
Exit Sub
End If
If TextBox5.Text = "" Then
MsgBox "Applicant Address field is not complete"
TextBox5.SetFocus
Exit Sub
End If

'If we got here, textboxes have valid input
'Put valid inputs into document
With ActiveDocument
.Bookmarks("NameOfApplicant").Range _
.InsertBefore TextBox1.Text
.Bookmarks("ContactName").Range _
.InsertBefore TextBox2.Text
.Bookmarks("ApplicantPhone").Range _
.InsertBefore TextBox3.Text
.Bookmarks("ApplicantFax").Range _
.InsertBefore TextBox4.Text
.Bookmarks("ApplicantAddress").Range _
.InsertBefore TextBox5
End With


''We got this far, must be ready to close the dialog
Unload Me
'End If

End Sub

 

[SkipVought]

Hi,

Here's a way...

Private Sub UserForm_Click()
    Dim ctr As Control, tNum As Integer, msg(4) As String
    
    msg(0) = "Name Of Applicant field is not complete"
    msg(1) = "Contact Name field is not complete"
    msg(2) = "Applicant Phone field is not complete"
    msg(3) = "Applicant Fax field is not complete"
    msg(4) = "Applicant Address field is not complete"
    
    For Each ctr In Controls
        If Left(ctr.Name, 7) = "TextBox" Then
            If ctr.Text = "" Then
                tNum = Right(ctr.Name, Len(ctr.Name) - 7)
                MsgBox msg(tNum)
            End If
        End If
        If Left(ctr.Name, 8) = "CheckBox" Then
            If Not ctr.Value Then
                tNum = Right(ctr.Name, Len(ctr.Name) - 8)
                MsgBox msg(tNum) & " for CheckBox"
            End If
        End If
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

You seem to have cross-posted this. This is not inherently wrong, but it IS considered rude not to mention it.

As I posted at VBAExpress, the value of a checkbox is either true or false, checked or unchecked. That is how you "verify that the user has put a check in the checkbox"

BTW: I would strongly recommend you take a very good look at what Skip posted. It is a far better design in that you can build a collection of error messages. Your code (as posted) is sequential, and error-traps and displays messages one at a time.

Say textbox1 is in error, but textbox 32 and textbox 50 are also in error.

As your code stands now, the textbox1 error will be caught, but NOT 32, or 50. textbox32 error will not be cuaght until - and ONLY until - it has tested correctly up to 32.

Although Skip's code gives a messagebox each time, which I would not do. I would build up a message. Like this:

Private Sub cmdCheckMe_Click()  [COLOR=red]' would not use Userform_Click[/color red]
    Dim ctr As Control, tNum As Integer, msg(4) As String
    [b]Dim myMSG As String[/b]
[COLOR=red] ' keep in mind this is a partiallisting[/color red]
    msg(0) = "Name Of Applicant field is not complete"
    msg(1) = "Contact Name field is not complete"
    msg(2) = "Applicant Phone field is not complete"
    msg(3) = "Applicant Fax field is not complete"
    For Each ctr In Controls
        If Left(ctr.Name, 7) = "TextBox" Then
            If ctr.Text = "" Then
                tNum = Right(ctr.Name, Len(ctr.Name) - 7)
               [b] myMSG = MyMSG & vbCrLf & msg(tNum)[/b]

That way you can display it all at once on ONE message, for example:

"Name Of Applicant field is not complete"
"Applicant Fax field is not complete"

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[rockwellsba]

Fumei (Gerry)- I apologize about the cross post. I was thinking as long as it was two different forums with different URLs (vbaexpress.com and tek-tips.com) it wasn't a problem.

I really appreciate your time and effort. I agree that one error message will be a cleaner wa of doing this. I will try it out.

Thanks again!!
-Phil

 

[strongm]

>the value of a checkbox is either true or false

A checkbox can also have a null value ... (although that doesn't have an impact in this particular case)

 

[fumei]

True, but only if you explicitly set TripleState = True (either as a property in the VBE, or programmatically), which is not the default.

For most people - IMO - there are very few instances where using TripleState on a checkbox is particularly useful. Further, I doubt if the average VBA-er is even aware of using TripleState.

But yes, you are correct of course, it can be Null.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry