I still have some problems with the check boxes.

A

Alain + Ivy

A MS Word XP (an agreement) has a userform attached from which I have to
select various "variable text" to personalize the agreement. I have found
how to use the text fields, the drop down lists and radio buttons and
bookmarks, but I still have some problems with the check boxes.

I have to check few boxes to select in which canadian province(s) the
agreement is valid. All provinces are listed on the userform with a blank
check box beside. The agreement may not be valid for all the provinces.

I am looking for the VBA code to have a specific narrative text be sent to a
specific location (bookmark) on the agreement if the check box is checked.

e.g. If the checkbox labeled "BC" is checked, then the text "Also valid in
British Columbia ..." has to be entered automaticaly in the agreement.

Hoping it is clear.

Thank in adavnce for any kind of advise.

Alain Kadlec
Ottawa
 
J

Jay Freedman

A MS Word XP (an agreement) has a userform attached from which I have to
select various "variable text" to personalize the agreement. I have found
how to use the text fields, the drop down lists and radio buttons and
bookmarks, but I still have some problems with the check boxes.

I have to check few boxes to select in which canadian province(s) the
agreement is valid. All provinces are listed on the userform with a blank
check box beside. The agreement may not be valid for all the provinces.

I am looking for the VBA code to have a specific narrative text be sent to a
specific location (bookmark) on the agreement if the check box is checked.

e.g. If the checkbox labeled "BC" is checked, then the text "Also valid in
British Columbia ..." has to be entered automaticaly in the agreement.

Hoping it is clear.

Thank in adavnce for any kind of advise.

Alain Kadlec
Ottawa
Click to expand...

For this example, I'll assume that you renamed the OK button to cmdOK,
and the check boxes have names like chkBC and chkOnt. In the
subroutine cmdOK_Click(), which runs when the user clicks the OK
button, I assume you already have code to insert variable text
associated with the text fields and other controls. Add code like the
following to handle the check boxes:

Private Sub cmdOK_Click()
Dim Clauses As String

' zero, one, or more of these If statements
' may add text to the string

If chkBC.Value = True Then
Clauses = Clauses & _
"Also valid in British Columbia ..." & vbCr
End If

If chkOnt.Value = True Then
Clauses = Clauses & _
"Also valid in Ontario ..." & vbCr
End If

' etc. ...

On Error Resume Next
If Len(Clauses) > 0 Then
' try to insert the string in the document
' at the bookmark
ActiveDocument.Bookmarks("bkProvince").Range.Text = Clauses

If Err.Number <> 0 Then
MsgBox _
"Could not find bookmark to insert Province clauses.", _
vbOKOnly + vbCritical, "Error"
End If
End If

Unload Me
End Sub
 
A

Alain + Ivy

Thanks a lot Jay. We are going to try this code. I would like to ask you a
question about the following code. This is what we have done so far before
you gave us your code. This code send the selected provinces (from a user
form) to the agreement to one bookmark only.. It works fine. The result is
something like that.

.........this agreement is only valid in the following province(s): Ontario
Nova Soctia Manitoba (in the same line)

My question is what is missing in this code to have the province listed like
this:
Ontario
Quebec
Manitoba
etc..

By using only one bookmark the lengh of the list will vary according to the
number of provinces that are selected.

We have tied the code equivalent to "Enter" but it was unsucessful. Not
sure where to add it.

Thank for your advise.

Alain Kadlec
Ottawa

------------------------------------------

Private Sub cmdCancel_Click()

Unload Me
ActiveDocument.Close SaveChanges:=False

End Sub

Private Sub cmdClear_Click()

txtContractStatus.Value = Null
txtInsurerName.Value = Null
txtPurchaserName.Value = Null
txtPurchaseDescription.Value = Null
chbNF.Value = False
chbPEI.Value = False
chbNS.Value = False
chbNB.Value = False
chbQB.Value = False
chbON.Value = False
chbMB.Value = False
chbSK.Value = False
chbAB.Value = False
chbBC.Value = False
chbYK.Value = False
chbNT.Value = False
chbNU.Value = False

End Sub

Private Sub cmdDetails1_Click()

MsgBox "Please read paragraph 6.1 before entering any information."

End Sub

Private Sub cmdDetails2_Click()

MsgBox "Please enter the legal name of the Insurance Company."

End Sub


Private Sub cmdDetails3_Click()

MsgBox "Please enter the name of the purchaser."

End Sub

Private Sub cmdDetails4_Click()

MsgBox "Please enter a short description of the insurance purchase."

End Sub

Private Sub cmdOK_Click()

Dim strchbNF As String
Dim strchbPEI As String
Dim strchbNS As String
Dim strchbNB As String
Dim strchbQB As String
Dim strchbON As String
Dim strchbMB As String
Dim strchbSK As String
Dim strchbAB As String
Dim strchbBC As String
Dim strchbYK As String
Dim strchbNT As String
Dim strchbNU As String

If chbNF = True Then strchbNF = " Newfoundland "
If chbPEI = True Then strchbPEI = " Prince Edward Island "
If chbNS = True Then strchbNS = " Nova Scotia "
If chbNB = True Then strchbNB = " New Brunswick "
If chbQB = True Then strchbQB = " Quebec "
If chbON = True Then strchbON = " Ontario "
If chbMB = True Then strchbMB = " Manitoba "
If chbSK = True Then strchbSK = " Saskatchewan "
If chbAB = True Then strchbAB = " Alberta "
If chbBC = True Then strchbBC = " British Columbia "
If chbYK = True Then strchbYK = " Yukon "
If chbNT = True Then strchbNT = " Northwest Territories "
If chbNU = True Then strchbNU = " Nunavut "

With ActiveDocument
.Bookmarks("ContractStatus").Range.Text = txtContractStatus.Value
.Bookmarks("InsurerName").Range.Text = txtInsurerName.Value
.Bookmarks("PurchaserName").Range.Text = txtPurchaserName.Value
.Bookmarks("PurchaseDescription").Range.Text =
txtPurchaseDescription.Value
.Bookmarks("Provinces").Range.Text = strchbNF
.Bookmarks("Provinces").Range.Text = strchbPEI
.Bookmarks("Provinces").Range.Text = strchbNS
.Bookmarks("Provinces").Range.Text = strchbNB
.Bookmarks("Provinces").Range.Text = strchbQB
.Bookmarks("Provinces").Range.Text = strchbON
.Bookmarks("Provinces").Range.Text = strchbMB
.Bookmarks("Provinces").Range.Text = strchbSK
.Bookmarks("Provinces").Range.Text = strchbAB
.Bookmarks("Provinces").Range.Text = strchbBC
.Bookmarks("Provinces").Range.Text = strchbYK
.Bookmarks("Provinces").Range.Text = strchbNT
.Bookmarks("Provinces").Range.Text = strchbNU
.Bookmarks("Provinces1").Range.Text = strchbNF
.Bookmarks("Provinces1").Range.Text = strchbPEI
.Bookmarks("Provinces1").Range.Text = strchbNS
.Bookmarks("Provinces1").Range.Text = strchbNB
.Bookmarks("Provinces1").Range.Text = strchbQB
.Bookmarks("Provinces1").Range.Text = strchbON
.Bookmarks("Provinces1").Range.Text = strchbMB
.Bookmarks("Provinces1").Range.Text = strchbSK
.Bookmarks("Provinces1").Range.Text = strchbAB
.Bookmarks("Provinces1").Range.Text = strchbBC
.Bookmarks("Provinces1").Range.Text = strchbYK
.Bookmarks("Provinces1").Range.Text = strchbNT
.Bookmarks("Provinces1").Range.Text = strchbNU

End With

Application.ScreenUpdating = True
Unload Me

End Sub

Private Sub UserForm_Initialize()

txtContractStatus.Value = "Valid"

End Sub
 
D

Doug Robbins

Modify the second and subsequent If chk constructions as follows:

Private Sub cmdOK_Click()
Dim Clauses As String

' zero, one, or more of these If statements
' may add text to the string

If chkBC.Value = True Then
Clauses = Clauses & _
"Also valid in British Columbia" & vbCr
End If

If chkOnt.Value = True Then
If Left(Clauses, 4) = "Also" Then
Clauses = Clauses & "Ontario" & vbCr
Else
Clauses = Clauses & _
"Also valid in Ontario" & vbCr
End If
End If

' etc. ...

On Error Resume Next
If Len(Clauses) > 0 Then
' try to insert the string in the document
' at the bookmark
ActiveDocument.Bookmarks("bkProvince").Range.Text = Clauses

If Err.Number <> 0 Then
MsgBox _
"Could not find bookmark to insert Province clauses.", _
vbOKOnly + vbCritical, "Error"
End If
End If

Unload Me
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
Alain + Ivy said:
Thanks a lot Jay. We are going to try this code. I would like to ask you
a
question about the following code. This is what we have done so far
before
you gave us your code. This code send the selected provinces (from a user
form) to the agreement to one bookmark only.. It works fine. The result
is
something like that.

........this agreement is only valid in the following province(s):
Ontario
Nova Soctia Manitoba (in the same line)

My question is what is missing in this code to have the province listed
like
this:
Ontario
Quebec
Manitoba
etc..

By using only one bookmark the lengh of the list will vary according to
the
number of provinces that are selected.

We have tied the code equivalent to "Enter" but it was unsucessful. Not
sure where to add it.

Thank for your advise.

Alain Kadlec
Ottawa

------------------------------------------

Private Sub cmdCancel_Click()

Unload Me
ActiveDocument.Close SaveChanges:=False

End Sub

Private Sub cmdClear_Click()

txtContractStatus.Value = Null
txtInsurerName.Value = Null
txtPurchaserName.Value = Null
txtPurchaseDescription.Value = Null
chbNF.Value = False
chbPEI.Value = False
chbNS.Value = False
chbNB.Value = False
chbQB.Value = False
chbON.Value = False
chbMB.Value = False
chbSK.Value = False
chbAB.Value = False
chbBC.Value = False
chbYK.Value = False
chbNT.Value = False
chbNU.Value = False

End Sub

Private Sub cmdDetails1_Click()

MsgBox "Please read paragraph 6.1 before entering any information."

End Sub

Private Sub cmdDetails2_Click()

MsgBox "Please enter the legal name of the Insurance Company."

End Sub


Private Sub cmdDetails3_Click()

MsgBox "Please enter the name of the purchaser."

End Sub

Private Sub cmdDetails4_Click()

MsgBox "Please enter a short description of the insurance purchase."

End Sub

Private Sub cmdOK_Click()

Dim strchbNF As String
Dim strchbPEI As String
Dim strchbNS As String
Dim strchbNB As String
Dim strchbQB As String
Dim strchbON As String
Dim strchbMB As String
Dim strchbSK As String
Dim strchbAB As String
Dim strchbBC As String
Dim strchbYK As String
Dim strchbNT As String
Dim strchbNU As String

If chbNF = True Then strchbNF = " Newfoundland "
If chbPEI = True Then strchbPEI = " Prince Edward Island "
If chbNS = True Then strchbNS = " Nova Scotia "
If chbNB = True Then strchbNB = " New Brunswick "
If chbQB = True Then strchbQB = " Quebec "
If chbON = True Then strchbON = " Ontario "
If chbMB = True Then strchbMB = " Manitoba "
If chbSK = True Then strchbSK = " Saskatchewan "
If chbAB = True Then strchbAB = " Alberta "
If chbBC = True Then strchbBC = " British Columbia "
If chbYK = True Then strchbYK = " Yukon "
If chbNT = True Then strchbNT = " Northwest Territories "
If chbNU = True Then strchbNU = " Nunavut "

With ActiveDocument
.Bookmarks("ContractStatus").Range.Text = txtContractStatus.Value
.Bookmarks("InsurerName").Range.Text = txtInsurerName.Value
.Bookmarks("PurchaserName").Range.Text = txtPurchaserName.Value
.Bookmarks("PurchaseDescription").Range.Text =
txtPurchaseDescription.Value
.Bookmarks("Provinces").Range.Text = strchbNF
.Bookmarks("Provinces").Range.Text = strchbPEI
.Bookmarks("Provinces").Range.Text = strchbNS
.Bookmarks("Provinces").Range.Text = strchbNB
.Bookmarks("Provinces").Range.Text = strchbQB
.Bookmarks("Provinces").Range.Text = strchbON
.Bookmarks("Provinces").Range.Text = strchbMB
.Bookmarks("Provinces").Range.Text = strchbSK
.Bookmarks("Provinces").Range.Text = strchbAB
.Bookmarks("Provinces").Range.Text = strchbBC
.Bookmarks("Provinces").Range.Text = strchbYK
.Bookmarks("Provinces").Range.Text = strchbNT
.Bookmarks("Provinces").Range.Text = strchbNU
.Bookmarks("Provinces1").Range.Text = strchbNF
.Bookmarks("Provinces1").Range.Text = strchbPEI
.Bookmarks("Provinces1").Range.Text = strchbNS
.Bookmarks("Provinces1").Range.Text = strchbNB
.Bookmarks("Provinces1").Range.Text = strchbQB
.Bookmarks("Provinces1").Range.Text = strchbON
.Bookmarks("Provinces1").Range.Text = strchbMB
.Bookmarks("Provinces1").Range.Text = strchbSK
.Bookmarks("Provinces1").Range.Text = strchbAB
.Bookmarks("Provinces1").Range.Text = strchbBC
.Bookmarks("Provinces1").Range.Text = strchbYK
.Bookmarks("Provinces1").Range.Text = strchbNT
.Bookmarks("Provinces1").Range.Text = strchbNU

End With

Application.ScreenUpdating = True
Unload Me

End Sub

Private Sub UserForm_Initialize()

txtContractStatus.Value = "Valid"

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check Boxes 1
Check boxes to text 1
Check Box and Validation of a cell 2
Userform Check Boxes 1
FORMS: Selecting check box locks text fields 0
sum of check boxes that have been checked 2
Likely a simple question on Check Boxes? 2
Check Boxes 1

Top