Checkbox Verify

[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 TextBox6.Text = "" Then
MsgBox "Applicant City field is not complete"
TextBox6.SetFocus
Exit Sub
End If
If TextBox7.Text = "" Then
MsgBox "Applicant State/Country is not complete"
TextBox7.SetFocus
Exit Sub
End If
If TextBox8.Text = "" Then
MsgBox "Applicant Zip Code field is not complete"
TextBox8.SetFocus
Exit Sub
End If
If TextBox9.Text = "" Then
MsgBox "Name Of Parent Company, if Subsidiary field is not complete"
TextBox8.SetFocus
Exit Sub
End If
If TextBox10.Text = "" Then
MsgBox "Corporation field needs an X or O"
TextBox10.SetFocus
Exit Sub
End If
If TextBox11.Text = "" Then
MsgBox "Partnership field needs an X or O"
TextBox11.SetFocus
Exit Sub
End If
If TextBox12.Text = "" Then
MsgBox "Sole Proprietor field needs an X or O"
TextBox12.SetFocus
Exit Sub
End If
If TextBox13.Text = "" Then
MsgBox "Type Of Buisness field is not complete"
TextBox13.SetFocus
Exit Sub
End If
If TextBox14.Text = "" Then
MsgBox "Years Established field is not complete"
TextBox14.SetFocus
Exit Sub
End If
If TextBox15.Text = "" Then
MsgBox "At Present Location Since field is not complete"
TextBox15.SetFocus
Exit Sub
End If
If TextBox16.Text = "" Then
MsgBox "Incorporated Tes or No field is not complete"
TextBox16.SetFocus
Exit Sub
End If
If TextBox17.Text = "" Then
MsgBox "If So, What State field is not complete"
TextBox17.SetFocus
Exit Sub
End If
If TextBox18.Text = "" Then
MsgBox "Fed ID field is not complete"
TextBox18.SetFocus
Exit Sub
End If
If TextBox19.Text = "" Then
MsgBox "Proprietor Partner or Officers Name field is not complete"
TextBox19.SetFocus
Exit Sub
End If
If TextBox20.Text = "" Then
MsgBox "Proprietor Partner or Officers Address field is not complete"
TextBox20.SetFocus
Exit Sub
End If
If TextBox21.Text = "" Then
MsgBox "Proprietor Partner or Officers SSN field is not complete"
TextBox21.SetFocus
Exit Sub
End If
If TextBox22.Text = "" Then
MsgBox "Proprietor Partner or Officers Drivers License No. field is not complete"
TextBox22.SetFocus
Exit Sub
End If
If TextBox23.Text = "" Then
MsgBox "Second Person Proprietor Partner or Officers Name field is not complete"
TextBox23.SetFocus
Exit Sub
End If
If TextBox24.Text = "" Then
MsgBox "Second Person Proprietor Partner or Officers Address field is not complete"
TextBox24.SetFocus
Exit Sub
End If
If TextBox25.Text = "" Then
MsgBox "Second Person Proprietor Partner or Officers SSN field is not complete"
TextBox25.SetFocus
Exit Sub
End If
If TextBox26.Text = "" Then
MsgBox "Second Person Proprietor Partner or Officers Drivers License No. field is not complete"
TextBox26.SetFocus
Exit Sub
End If
If TextBox27.Text = "" Then
MsgBox "Bank Reference Bank Name field is not complete"
TextBox27.SetFocus
Exit Sub
End If
If TextBox28.Text = "" Then
MsgBox "Bank Phone field is not complete"
TextBox28.SetFocus
Exit Sub
End If
If TextBox29.Text = "" Then
MsgBox "Bank Fax field is not complete"
TextBox29.SetFocus
Exit Sub
End If
If TextBox30.Text = "" Then
MsgBox "Acctount Number field is not complete"
TextBox30.SetFocus
Exit Sub
End If
If TextBox31.Text = "" Then
MsgBox "BankA ddress field is not complete"
TextBox31.SetFocus
Exit Sub
End If
If TextBox32.Text = "" Then
MsgBox "Bank City field is not complete"
TextBox32.SetFocus
Exit Sub
End If
If TextBox33.Text = "" Then
MsgBox "Bank State/Country field is not complete"
TextBox33.SetFocus
Exit Sub
End If
If TextBox34.Text = "" Then
MsgBox "Bank Zip field is not complete"
TextBox34.SetFocus
Exit Sub
End If
If TextBox35.Text = "" Then
MsgBox "Trade Reference One: Name field is not complete"
TextBox35.SetFocus
Exit Sub
End If
If TextBox36.Text = "" Then
MsgBox "Trade Reference One: Phone is not complete"
TextBox36.SetFocus
Exit Sub
End If
If TextBox37.Text = "" Then
MsgBox "Trade Reference One: Fax field is not complete"
TextBox37.SetFocus
Exit Sub
End If
If TextBox38.Text = "" Then
MsgBox "Trade Reference One: Address field is not complete"
TextBox38.SetFocus
Exit Sub
End If
If TextBox39.Text = "" Then
MsgBox "Trade Reference One: City field is not complete"
TextBox39.SetFocus
Exit Sub
End If
If TextBox40.Text = "" Then
MsgBox "Trade Reference One: State/Country field is not complete"
TextBox40.SetFocus
Exit Sub
End If
If TextBox41.Text = "" Then
MsgBox "Trade Reference One: Zip Code field is not complete"
TextBox41.SetFocus
Exit Sub
End If
If TextBox42.Text = "" Then
MsgBox "Trade Reference Two: Name field is not complete"
TextBox42.SetFocus
Exit Sub
End If
If TextBox43.Text = "" Then
MsgBox "Trade Reference Two: Phone field is not complete"
TextBox43.SetFocus
Exit Sub
End If
If TextBox44.Text = "" Then
MsgBox "TTrade Reference Two: Fax field is not complete"
TextBox44.SetFocus
Exit Sub
End If
If TextBox45.Text = "" Then
MsgBox "Trade Reference Two: Address field is not complete"
TextBox45.SetFocus
Exit Sub
End If
If TextBox46.Text = "" Then
MsgBox "Trade Reference Two: City field is not complete"
TextBox46.SetFocus
Exit Sub
End If
If TextBox47.Text = "" Then
MsgBox "Trade Reference Two: State/Country field is not complete"
TextBox47.SetFocus
Exit Sub
End If
If TextBox48.Text = "" Then
MsgBox "Trade Reference Two: Zip Code field is not complete"
TextBox48.SetFocus
Exit Sub
End If
If TextBox49.Text = "" Then
MsgBox "Trade Reference Three: Name field is not complete"
TextBox49.SetFocus
Exit Sub
End If
If TextBox50.Text = "" Then
MsgBox "Trade Reference Three: Phone field is not complete"
TextBox50.SetFocus
Exit Sub
End If
If TextBox51.Text = "" Then
MsgBox "Trade Reference Three: Fax field is not complete"
TextBox51.SetFocus
Exit Sub
End If
If TextBox52.Text = "" Then
MsgBox "Trade Reference Three: Address field is not complete"
TextBox52.SetFocus
Exit Sub
End If
If TextBox53.Text = "" Then
MsgBox "Trade Reference Three: City field is not complete"
TextBox53.SetFocus
Exit Sub
End If
If TextBox54.Text = "" Then
MsgBox "Trade Reference Three: State/Country field is not complete"
TextBox54.SetFocus
Exit Sub
End If
If TextBox55.Text = "" Then
MsgBox "Trade Reference Three: Zip Code field is not complete"
TextBox55.SetFocus
Exit Sub
End If
' If TextBox56.Text = "" Then
' MsgBox "Zip field is not complete"
' TextBox56.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
.Bookmarks("ApplicantCity").Range _
.InsertBefore TextBox6
.Bookmarks("ApplicantStateCntry").Range _
.InsertBefore TextBox7
.Bookmarks("ApplicantZip").Range _
.InsertBefore TextBox8
.Bookmarks("NameOfParentCompany").Range _
.InsertBefore TextBox9
.Bookmarks("Corporation").Range _
.InsertBefore TextBox10
.Bookmarks("Partnership").Range _
.InsertBefore TextBox11
.Bookmarks("SoleProprietor").Range _
.InsertBefore TextBox12
.Bookmarks("TypeOfBuisness").Range _
.InsertBefore TextBox13
.Bookmarks("YearsEst").Range _
.InsertBefore TextBox14
.Bookmarks("AtPresentLocationSince").Range _
.InsertBefore TextBox15
.Bookmarks("Incorporated").Range _
.InsertBefore TextBox16
.Bookmarks("IfSoWhatState").Range _
.InsertBefore TextBox17
.Bookmarks("FedID").Range _
.InsertBefore TextBox18
.Bookmarks("ProprietorPartnerOrOfficersName").Range _
.InsertBefore TextBox19
.Bookmarks("ProprietorPartnerOrOfficersAddress").Range _
.InsertBefore TextBox20
.Bookmarks("ProprietorPartnerOrOfficersSSN").Range _
.InsertBefore TextBox21
.Bookmarks("ProprietorPartnerOrOfficersDLNo").Range _
.InsertBefore TextBox22
.Bookmarks("SecondProprietorPartnerOrOfficersName").Range _
.InsertBefore TextBox23
.Bookmarks("SecondProprietorPartnerOrOfficersAddress").Range _
.InsertBefore TextBox24
.Bookmarks("SecondProprietorPartnerOrOfficersSSN").Range _
.InsertBefore TextBox25
.Bookmarks("SecondProprietorPartnerOrOfficersDLNo").Range _
.InsertBefore TextBox26
.Bookmarks("BankRefBank").Range _
.InsertBefore TextBox27
.Bookmarks("BankRefPhone").Range _
.InsertBefore TextBox28
.Bookmarks("BankRefFax").Range _
.InsertBefore TextBox29
.Bookmarks("BankAcctNo").Range _
.InsertBefore TextBox30
.Bookmarks("BankAddress").Range _
.InsertBefore TextBox31
.Bookmarks("BankCity").Range _
.InsertBefore TextBox32
.Bookmarks("BankStateCntry").Range _
.InsertBefore TextBox33
.Bookmarks("BankZip").Range _
.InsertBefore TextBox34
.Bookmarks("TradeRefOneName").Range _
.InsertBefore TextBox35
.Bookmarks("TradeRefOnePhone").Range _
.InsertBefore TextBox36
.Bookmarks("TradeRefOneFax").Range _
.InsertBefore TextBox37
.Bookmarks("TradeRefOneAddress").Range _
.InsertBefore TextBox38
.Bookmarks("TradeRefOneCuty").Range _
.InsertBefore TextBox39
.Bookmarks("TradeRefOneStateCntry").Range _
.InsertBefore TextBox40
.Bookmarks("TradeRefOneZip").Range _
.InsertBefore TextBox41
.Bookmarks("TradeRefTwoName").Range _
.InsertBefore TextBox42
.Bookmarks("TradeRefTwoPhone").Range _
.InsertBefore TextBox43
.Bookmarks("TradeRefTwoFax").Range _
.InsertBefore TextBox44
.Bookmarks("TradeRefTwoAddress").Range _
.InsertBefore TextBox45
.Bookmarks("TradeRefTwoCity").Range _
.InsertBefore TextBox46
.Bookmarks("TradeRefTwoStateCntry").Range _
.InsertBefore TextBox47
.Bookmarks("TradeRefTwoZip").Range _
.InsertBefore TextBox48
.Bookmarks("TradeRefThreeName").Range _
.InsertBefore TextBox49
.Bookmarks("TradeRefThreePhone").Range _
.InsertBefore TextBox50
.Bookmarks("TradeRefThreeFax").Range _
.InsertBefore TextBox51
.Bookmarks("TradeRefThreeAddress").Range _
.InsertBefore TextBox52
.Bookmarks("TradeRefThreeCity").Range _
.InsertBefore TextBox53
.Bookmarks("TradeRefThreeStateCntry").Range _
.InsertBefore TextBox54
.Bookmarks("TradeRefThreeZip").Range _
.InsertBefore TextBox55
End With


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


End Sub

[fumei]

If CheckBox1.Result = False Then

A checkbox result is either true (checked) or false (unchecked).

You, as it stands it could (although unlikely) that your user would have to click the commandbutton 55 times before you know the all textboxes are not blank.

I have to point out that your comment:

'If we got here, textboxes have valid input

is not accurate. You do not know they have valid input. All you know is that they are not blank, that is all. For example, textbox32...

If TextBox32.Text = "" Then
MsgBox "Bank City field is not complete"
If someone entered "New Yprk" (the "p instead of the "o"; or "Neew York", or "Nw York", or "Mew York") by mistake, all of those are "valid" input.

[rockwellsba]

Funei- I agree. I was aware that the user can type anything they want into the TextBox and it would be called "valid". It's OK as long as it's not blank. Thanks for the response.

[geekgirlau]

You could dramatically streamline this code by using logical names for your controls. In this example, I've made the assumption that every bookmark matches a control, so the names follow the same naming convention.

I haven't included the checkboxes as I don't know the logic for those ...


Private Sub CommandButton1_Click()
Dim ctl As Control


' check all controls on the form
For Each ctl In Me.Controls
' assumes that every text box name starts with "txt"
If UCase(Left(ctl.Name,3)) = "TXT" Then
If ctl = "" Then
' not necessary to have individual messages, as the focus moves to the control
' that is blank
MsgBox "Required field incomplete"

ctl.SetFocus
GoTo Incomplete
End If
End If
Next ctl

' Insert inputs into document
' ASSUMPTION: Every text box has a name that matches the bookmark name, but with "txt"
' in front of it. For example, the text box "txtNameOfApplicant" should populate the
' bookmark "NameOfApplicant".

For Each ctl In Me.Controls
ActiveDocument.Bookmarks(Mid(ctl.Name, 4)).Range.InsertBefore ctl.Text
Next ctl
' close the user form
Unload Me
Incomplete:
End Sub

[fumei]

Yipee!

Indeed, indeed. Matching textbox names to bookmarks is an excellent way to streamline code.