Populating userform depending on document contents

[Patrickw]

I have a document addressing either one or two people.

Within the body of the document, you can find one of the following
references:

John Doe
or
John Doe and Jane Doe

I'd like to parse and recover the name of an individual in a certain
line of the document. If the name is that of an individual, then I'd
like to place the name in the userform and make sure labels, for
instance, are changed to reflect the singular. Conversely, if the
names are conjunctive (as in a husband and wife), I'd like to place
their names in the userform and make sure labels and other controls
reflect plural usages.

 

[Greg Maxey]

Assuming the reference is defined by a bookmark range (bookmark named
"Addressee") you could use something like this in the UserForm Initialize
event:

Private Sub UserForm_Initialize()
If InStr(ActiveDocument.Bookmarks("Addressee").Range.Text, "and") > 1 Then
Me.lblAddressee = "Addressees"
Else
Me.lblAddressee = "Addressee"
End If
Me.txtAddressee = ActiveDocument.Bookmarks("Addressee").Range.Text
End Sub

I have a document addressing either one or two people.

Within the body of the document, you can find one of the following
references:

John Doe
or
John Doe and Jane Doe

I'd like to parse and recover the name of an individual in a certain
line of the document. If the name is that of an individual, then I'd
like to place the name in the userform and make sure labels, for
instance, are changed to reflect the singular. Conversely, if the
names are conjunctive (as in a husband and wife), I'd like to place
their names in the userform and make sure labels and other controls
reflect plural usages.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Greg Maxey]

Assuming the reference is defined by a bookmark range (bookmark named
"Addressee") you could use something like this in the UserForm Initialize
event:

Private Sub UserForm_Initialize()
If InStr(ActiveDocument.Bookmarks("Addressee").Range.Text, "and") > 1 Then
Me.lblAddressee = "Addressees"
Else
Me.lblAddressee = "Addressee"
End If
Me.txtAddressee = ActiveDocument.Bookmarks("Addressee").Range.Text
End Sub

I have a document addressing either one or two people.

Within the body of the document, you can find one of the following
references:

John Doe
or
John Doe and Jane Doe

I'd like to parse and recover the name of an individual in a certain
line of the document. If the name is that of an individual, then I'd
like to place the name in the userform and make sure labels, for
instance, are changed to reflect the singular. Conversely, if the
names are conjunctive (as in a husband and wife), I'd like to place
their names in the userform and make sure labels and other controls
reflect plural usages.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Greg Maxey]

Assuming the reference is defined by a bookmark range (bookmark named
"Addressee") you could use something like this in the UserForm Initialize
event:

Private Sub UserForm_Initialize()
If InStr(ActiveDocument.Bookmarks("Addressee").Range.Text, "and") > 1 Then
Me.lblAddressee = "Addressees"
Else
Me.lblAddressee = "Addressee"
End If
Me.txtAddressee = ActiveDocument.Bookmarks("Addressee").Range.Text
End Sub

I have a document addressing either one or two people.

Within the body of the document, you can find one of the following
references:

John Doe
or
John Doe and Jane Doe

I'd like to parse and recover the name of an individual in a certain
line of the document. If the name is that of an individual, then I'd
like to place the name in the userform and make sure labels, for
instance, are changed to reflect the singular. Conversely, if the
names are conjunctive (as in a husband and wife), I'd like to place
their names in the userform and make sure labels and other controls
reflect plural usages.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Patrickw]

Sorry, forgot to mention that bookmarks and fields are disabled
because the document has been generated from a mailmerge. The text
names in the document appear several times in the document as a part
of a paragraph and is also contained within the cell of an MSWord
table. In any event, they always are merged into the document in the
same position.

 

[Greg Maxey]

You should be able to apply a variation of:

If InStr(ActiveDocument.Bookmarks("Addressee").Range.Text, "and") > 1 Then

using the Cell reference of the table that the text always appears in e.g.,

If InStr(ActiveDocument.Tables(1).Cells(2, 2).Range.Text, "and") > 1 Then

The above example looks at the first tables/second row/second column text.

Sorry, forgot to mention that bookmarks and fields are disabled
because the document has been generated from a mailmerge. The text
names in the document appear several times in the document as a part
of a paragraph and is also contained within the cell of an MSWord
table. In any event, they always are merged into the document in the
same position.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Patrickw]

Created a two column, two row table in a document and populated each
cell:

___________________________________________________________
| | |
| John Doe and Jane Doe | John Doe |
|___________________________|_____________________________|
| | |
| Jane Doe | Cell(2,2) |
|___________________________|_____________________________|

Created a userform with a label "lblAddressee" and a textbox
"txtAddressee"

Used following code:


Private Sub UserForm1_Initialize()

If InStr(ActiveDocument.Tables(1).Cells(1, 1).Range.Text, "and") >
1 Then
Me.lblAddressee = "Addressees"
Else
Me.lblAddressee = "Addressee"
End If
Me.txtAddressee = ActiveDocument.Tables(1).Cells(1,
1).Range.Text

End Sub

When the userform pops up, I just get the default label "Label1" and a
blank textbox.
Tried this, too:

Private Sub UserForm1_Initialize()

If InStr(ActiveDocument.Tables(1).Cells(1, 1).Range.Text, "and") >
1 Then
Me.lblAddressee.Caption = "Addressees"
Else
Me.lblAddressee.Caption = "Addressee"
End If
Me.txtAddressee.Value = ActiveDocument.Tables(1).Cells(1,
1).Range.Text

End Sub

Still get the defaults

 

[Greg Maxey]

Patrickw,

Some carelessness on my part. I wasn't using the editor and didn't test
("Cells" should be "Cell"). This should work if Table 1 First Row First
Column is poplulated with John Doe and Jane Doe:

Private Sub UserForm_Initialize()
If InStr(ActiveDocument.Tables(1).Cell(1, 1).Range.Text, "and") > 1 Then
Me.lblAddressee.Caption = "Addressees"
Else
Me.lblAddressee.Caption = "Addressee"
End If
Me.txtAddressee.Text = ActiveDocument.Tables(1).Cell(1, 1).Range.Text
End Sub

Created a two column, two row table in a document and populated each
cell:

___________________________________________________________

Created a userform with a label "lblAddressee" and a textbox
"txtAddressee"

Used following code:


Private Sub UserForm1_Initialize()

If InStr(ActiveDocument.Tables(1).Cells(1, 1).Range.Text, "and") >
1 Then
Me.lblAddressee = "Addressees"
Else
Me.lblAddressee = "Addressee"
End If
Me.txtAddressee = ActiveDocument.Tables(1).Cells(1,
1).Range.Text

End Sub

When the userform pops up, I just get the default label "Label1" and a
blank textbox.
Tried this, too:

Private Sub UserForm1_Initialize()

If InStr(ActiveDocument.Tables(1).Cells(1, 1).Range.Text, "and") >
1 Then
Me.lblAddressee.Caption = "Addressees"
Else
Me.lblAddressee.Caption = "Addressee"
End If
Me.txtAddressee.Value = ActiveDocument.Tables(1).Cells(1,
1).Range.Text

End Sub

Still get the defaults

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the
strong man stumbles, or where the doer of deeds could have done them
better. The credit belongs to the man in the arena, whose face is
marred by dust and sweat and blood, who strives valiantly...who knows
the great enthusiasms, the great devotions, who spends himself in a
worthy cause, who at the best knows in the end the triumph of high
achievement, and who at the worst, if he fails, at least fails while
daring greatly, so that his place shall never be with those cold and
timid souls who have never known neither victory nor defeat." - TR

 

[Patrickw]

Got it working with the following:


If InStr(ActiveDocument.Tables(7).Cell(10, 1).Range.Text, "and") >
1 Then
Me.OPTDebtorJoint.Value = True
Else
Me.OPTDebtorIndividual.Value = True
End If
Me.FRMClientAppt.Caption = "Appointment with " +
ActiveDocument.Tables(7).Cell(10, 1).Range.Text

Except the FRMClientAppt.Caption adds two box characters at the end of
the string.

 

[Patrickw]

FRMClientAppt is a frame object, by the way.

I have a complex document and had to experiment to find the right
number for the table. It turned out to be 7, even though I only
counted 4 tables appearing prior to the table I wanted to reference.
Anyway, it works, except for the little box characters appended to the
end of the caption.

 

[Patrickw]

It works flawlessly with a label control, but for some reason, when I
try to change the caption of a frame control, I get the following:

John Doe and Jane Doe[][]

where a pair of double brackets represents a box shaped character

 

[Patrickw]

Figured it out. Needed to strip the characters off the string. Ended
up with the following:

Private Sub UserForm_Initialize()

strClientName = ActiveDocument.Tables(7).Cell(10, 1).Range.Text
strClientName = Left(strClientName, Len(strClientName) - 2)

If InStr(ActiveDocument.Tables(7).Cell(10, 1).Range.Text, "and") >
1 Then
booJointDebtor = True
Me.FRMPropertyNecessary.Caption = "The property is necessary
to the effective reorganization of " + strClientName + " because it is
their only mode of transportation"
Else
booJointDebtor = False
Me.FRMPropertyNecessary.Caption = "The property is necessary
to the effective reorganization of " + strClientName + " because it is
" + strClientName + "'s only mode of transportation"
End If
Me.FRMClientAppt.Caption = "Appointment with " + strClientName + "
to sign affidavit"
Me.LBLResponse1.Caption = strClientName
Me.LBLResponse2.Caption = strClientName
Me.LBLResponse3.Caption = strClientName

End Sub

Thanks for resolving the main stumbling block and setting me in the
right direction

 

[Patrickw]

Thought I'd close out the thread by posting my final code. This code
even converts the text to Proper (Title) Case and excepts the word
"and", so that I begin with text in the document cell as follows:

JOHN DOE and JANE DOE

and end up with the following text for label and frame captions:

John Doe and Jane Doe

Private Sub UserForm_Initialize()

strClientName = ActiveDocument.Tables(7).Cell(10, 1).Range.Text
strClientName = Left(strClientName, Len(strClientName) - 2)
' Convert strClientName to Title Case
strClientName = StrConv(strClientName, vbProperCase)
' Convert 'And' to lower case 'and'
If InStr(1, strClientName, "And", 0) > 0 Then
strClientName = Mid(strClientName, 1, InStr(1, strClientName,
"And", 0) - 1) & LCase("And") & Mid(strClientName, InStr(1,
strClientName, "And", 0) + Len("And"), 1000000)
End If


If InStr(ActiveDocument.Tables(7).Cell(10, 1).Range.Text, "and") >
1 Then
booJointDebtor = True
Me.FRMPropertyNecessary.Caption = "The property is necessary
to the effective reorganization of " + strClientName + " because it is
their"
Else
booJointDebtor = False
Me.FRMPropertyNecessary.Caption = "The property is necessary
to the effective reorganization of " + ActiveDocument.Tables(7).Cell
(10, 1).Range.Text + " because it is " + ActiveDocument.Tables(7).Cell
(10, 1).Range.Text + "'s"
End If
Me.FRMClientAppt.Caption = "Appointment with " + strClientName + "
to sign affidavit"
Me.LBLResponse1.Caption = strClientName
Me.LBLResponse2.Caption = strClientName
Me.LBLResponse3.Caption = strClientName

End Sub

 

[Greg Maxey]

PatrickW,

I don't know where you are posting from, but you stayed at it longer than me
because of the late hour here . I'm glad you got it working so well and
learned a few things in the process.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

"It is not the critic who counts, not the man who points out how the strong
man stumbles, or where the doer of deeds could have done them better. The
credit belongs to the man in the arena, whose face is marred by dust and
sweat and blood, who strives valiantly...who knows the great enthusiasms,
the great devotions, who spends himself in a worthy cause, who at the best
knows in the end the triumph of high achievement, and who at the worst, if
he fails, at least fails while daring greatly, so that his place shall never
be with those cold and timid souls who have never known neither victory nor
defeat." - TR