Insert sentence only if text field is not null

My searching is coming up empty, because I don't know what my intended result
is called. Can someone tell me the name of what I'm trying to do, and direct
me to the right reading? I want to insert some text if a certain field is
not null. I also want to insert the contents of that field in the
conditional text, i.e.:

...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
singly or collectively].

Thanks in advance.
Sandi

Doug
Thu Jul 10 12:49:50 PDT 2008

What type of field/or form are you talking about?

--
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

"Sandi V" <SandiV@discussions.microsoft.com> wrote in message
news:68069ECC-92EF-40CA-B896-42B7A9CD3F79@microsoft.com...
> My searching is coming up empty, because I don't know what my intended
> result
> is called. Can someone tell me the name of what I'm trying to do, and
> direct
> me to the right reading? I want to insert some text if a certain field is
> not null. I also want to insert the contents of that field in the
> conditional text, i.e.:
>
> ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
> singly or collectively].
>
> Thanks in advance.
> Sandi
>
>
>

SandiV
Thu Jul 10 13:10:01 PDT 2008

Sorry, I'm making the user form in VBA for a Word template. Whatever the
user enters into a text box on the user form gets passed to a bookmark in
Word.

"Doug Robbins - Word MVP" wrote:

> What type of field/or form are you talking about?
>
> --
> 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
>
> "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> news:68069ECC-92EF-40CA-B896-42B7A9CD3F79@microsoft.com...
> > My searching is coming up empty, because I don't know what my intended
> > result
> > is called. Can someone tell me the name of what I'm trying to do, and
> > direct
> > me to the right reading? I want to insert some text if a certain field is
> > not null. I also want to insert the contents of that field in the
> > conditional text, i.e.:
> >
> > ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
> > singly or collectively].
> >
> > Thanks in advance.
> > Sandi
> >
> >
> >
>
>
>

Doug
Thu Jul 10 18:10:17 PDT 2008

It would be easier if you use a Docvariable field instead of a bookmark for
this

To do that, insert a { DOCVARIABLE varBorrower } field into the template
(using Ctrl+F9 to insert the field delimiters { } and Alt+F9 to toggle off
their display.

Then in the userform code, use

With ActiveDocument
.Variables("varBorrower").Value = txtBorrower.Text
If Len(txtBorrower2.Text) > 0 Then
With .Variables("varBorrower")
.Value = .Value & "[ and " & txtBorrower2.Text & " each singly
or collectively]"
End With
End If
.Range.Fields.Update
End With

Assuming that the borrower's names are entered into controls named
txtBorrower and txtBorrower2.

I suppose with bookmarks you could use just one bookmark BKBORROWER

With ActiveDocument.Bookmarks(":BKBORROWER").Range
If Len(txtBorrower2.Text) > 0 then
.InsertBefore txtBorrower.Text & "[ and " & txtBorrower2.Text & "
each singly or collectively]"
Else
.InsertBefore txtBorrower.Text
End If
End With



--
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

"Sandi V" <SandiV@discussions.microsoft.com> wrote in message
news:73B331B2-50CF-4FF2-BBEF-9FCB64A1A1E7@microsoft.com...
> Sorry, I'm making the user form in VBA for a Word template. Whatever the
> user enters into a text box on the user form gets passed to a bookmark in
> Word.
>
> "Doug Robbins - Word MVP" wrote:
>
>> What type of field/or form are you talking about?
>>
>> --
>> 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
>>
>> "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
>> news:68069ECC-92EF-40CA-B896-42B7A9CD3F79@microsoft.com...
>> > My searching is coming up empty, because I don't know what my intended
>> > result
>> > is called. Can someone tell me the name of what I'm trying to do, and
>> > direct
>> > me to the right reading? I want to insert some text if a certain field
>> > is
>> > not null. I also want to insert the contents of that field in the
>> > conditional text, i.e.:
>> >
>> > ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
>> > singly or collectively].
>> >
>> > Thanks in advance.
>> > Sandi
>> >
>> >
>> >
>>
>>
>>

gordon(dot)bentleymix(at)gmail(dot)com>
Thu Jul 10 19:13:00 PDT 2008

So many possible solutions... ;-P

The one I like (and usually use) looks like this.

Assume a UserForm with TextBoxes called txtBKBorrower and txtBKBorrower2 and
a single bookmark in the document called BKBORROWER with the requisite space
before the bookmark and a full-stop after; e.g.:

...renew extensions of credit to the [BKBORROWER].

Adapt the following code according to your own needs:

Private Sub InsertBKBorrower()
Dim myRange As Range
With ActiveDocument
If .Bookmarks.Exists("BKBORROWER") Then
myRange = .Bookmarks("BKBORROWER").Range
myRange.Text = fcnBuildBKBorrower
.Bookmarks.Add "BKBORROWER", myRange
End If
End With
End Sub

Private Function fcnBuildBKBorrower() As String
Dim Temp As String
Temp = txtBKBorrower.Value
If Len(txtBKBorrower2.Value) > 0 Then
Temp = Temp & " and " & txtBKBorrower2.Value & ", each singly or
collectively"
End If
fcnBuildBKBorrower = Temp
End Function

You could probably do without the code to reinsert the bookmark after
writing the text into it, but preserving it this way has certain advantages -
none of which are necessarily applicable to your situation.

And you could probably get away without checking for the existence of the
bookmark first - especially since there's no error handling in place if it's
not there - but it's good practice to do it this way. Word doesn't throw an
error if it can't find a specified bookmark; it just terminates execution of
the procedure without warning, which can be devilishly difficult to debug.
Checking first at least ensures that the rest of the code in the sub is
executed.

Also the use of a function for building the Borrower probably isn't
necessary, but there are times when knowing how to do this comes in very
handy. For example, if you need to insert similar content in several
different places, you could build a "generic" function that accepts arguments
and call it multiple times instead of writing the same code repeatedly.

Finally, Doug's suggestion to use a DOCVARIABLE fields is a good approach as
well and could be modified to work with my code, but it can be a bit more
difficult for the less experienced VBA programmer to understand. Document
variables aren't as "visible" as bookmarks, and consequently, it's easier to
get something slightly wrong without a clear indication as to what the
problem is. (With a bookmark you can use native Word functionality to find
it, check its spelling, etc., but doc vars and really only be "interrogated"
through the VBE.)

Of course there are probably as many ways to meet this requirement as there
are programmers. No one way is really the best - although some are certainly
worse than others. In the end it comes down to what you're most comfortable
with.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


"Sandi V" wrote:

> My searching is coming up empty, because I don't know what my intended result
> is called. Can someone tell me the name of what I'm trying to do, and direct
> me to the right reading? I want to insert some text if a certain field is
> not null. I also want to insert the contents of that field in the
> conditional text, i.e.:
>
> ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
> singly or collectively].
>
> Thanks in advance.
> Sandi
>
>
>

SandiV
Fri Jul 11 05:38:00 PDT 2008

Thank you! I'll give that a try; but probably not until next week.

"Doug Robbins - Word MVP" wrote:

> It would be easier if you use a Docvariable field instead of a bookmark for
> this
>
> To do that, insert a { DOCVARIABLE varBorrower } field into the template
> (using Ctrl+F9 to insert the field delimiters { } and Alt+F9 to toggle off
> their display.
>
> Then in the userform code, use
>
> With ActiveDocument
> .Variables("varBorrower").Value = txtBorrower.Text
> If Len(txtBorrower2.Text) > 0 Then
> With .Variables("varBorrower")
> .Value = .Value & "[ and " & txtBorrower2.Text & " each singly
> or collectively]"
> End With
> End If
> .Range.Fields.Update
> End With
>
> Assuming that the borrower's names are entered into controls named
> txtBorrower and txtBorrower2.
>
> I suppose with bookmarks you could use just one bookmark BKBORROWER
>
> With ActiveDocument.Bookmarks(":BKBORROWER").Range
> If Len(txtBorrower2.Text) > 0 then
> .InsertBefore txtBorrower.Text & "[ and " & txtBorrower2.Text & "
> each singly or collectively]"
> Else
> .InsertBefore txtBorrower.Text
> End If
> End With
>
>
>
> --
> 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
>
> "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> news:73B331B2-50CF-4FF2-BBEF-9FCB64A1A1E7@microsoft.com...
> > Sorry, I'm making the user form in VBA for a Word template. Whatever the
> > user enters into a text box on the user form gets passed to a bookmark in
> > Word.
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> >> What type of field/or form are you talking about?
> >>
> >> --
> >> 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
> >>
> >> "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> >> news:68069ECC-92EF-40CA-B896-42B7A9CD3F79@microsoft.com...
> >> > My searching is coming up empty, because I don't know what my intended
> >> > result
> >> > is called. Can someone tell me the name of what I'm trying to do, and
> >> > direct
> >> > me to the right reading? I want to insert some text if a certain field
> >> > is
> >> > not null. I also want to insert the contents of that field in the
> >> > conditional text, i.e.:
> >> >
> >> > ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
> >> > singly or collectively].
> >> >
> >> > Thanks in advance.
> >> > Sandi
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

SandiV
Fri Jul 11 05:42:00 PDT 2008

"Of course there are probably as many ways to meet this requirement as there
> are programmers. No one way is really the best - although some are certainly
> worse than others. In the end it comes down to what you're most comfortable
> with."

Thanks, I'll play with both. I only dabble in VBA every six months or so;
it's like learning it all over every time. I wouldn't be able to get through
it without the help of discussion boards.


"Gordon Bentley-Mix" wrote:

> So many possible solutions... ;-P
>
> The one I like (and usually use) looks like this.
>
> Assume a UserForm with TextBoxes called txtBKBorrower and txtBKBorrower2 and
> a single bookmark in the document called BKBORROWER with the requisite space
> before the bookmark and a full-stop after; e.g.:
>
> ...renew extensions of credit to the [BKBORROWER].
>
> Adapt the following code according to your own needs:
>
> Private Sub InsertBKBorrower()
> Dim myRange As Range
> With ActiveDocument
> If .Bookmarks.Exists("BKBORROWER") Then
> myRange = .Bookmarks("BKBORROWER").Range
> myRange.Text = fcnBuildBKBorrower
> .Bookmarks.Add "BKBORROWER", myRange
> End If
> End With
> End Sub
>
> Private Function fcnBuildBKBorrower() As String
> Dim Temp As String
> Temp = txtBKBorrower.Value
> If Len(txtBKBorrower2.Value) > 0 Then
> Temp = Temp & " and " & txtBKBorrower2.Value & ", each singly or
> collectively"
> End If
> fcnBuildBKBorrower = Temp
> End Function
>
> You could probably do without the code to reinsert the bookmark after
> writing the text into it, but preserving it this way has certain advantages -
> none of which are necessarily applicable to your situation.
>
> And you could probably get away without checking for the existence of the
> bookmark first - especially since there's no error handling in place if it's
> not there - but it's good practice to do it this way. Word doesn't throw an
> error if it can't find a specified bookmark; it just terminates execution of
> the procedure without warning, which can be devilishly difficult to debug.
> Checking first at least ensures that the rest of the code in the sub is
> executed.
>
> Also the use of a function for building the Borrower probably isn't
> necessary, but there are times when knowing how to do this comes in very
> handy. For example, if you need to insert similar content in several
> different places, you could build a "generic" function that accepts arguments
> and call it multiple times instead of writing the same code repeatedly.
>
> Finally, Doug's suggestion to use a DOCVARIABLE fields is a good approach as
> well and could be modified to work with my code, but it can be a bit more
> difficult for the less experienced VBA programmer to understand. Document
> variables aren't as "visible" as bookmarks, and consequently, it's easier to
> get something slightly wrong without a clear indication as to what the
> problem is. (With a bookmark you can use native Word functionality to find
> it, check its spelling, etc., but doc vars and really only be "interrogated"
> through the VBE.)
>
> Of course there are probably as many ways to meet this requirement as there
> are programmers. No one way is really the best - although some are certainly
> worse than others. In the end it comes down to what you're most comfortable
> with.
> --
> Cheers!
> Gordon
>
> Uninvited email contact will be marked as SPAM and ignored. Please post all
> follow-ups to the newsgroup.
>
>
> "Sandi V" wrote:
>
> > My searching is coming up empty, because I don't know what my intended result
> > is called. Can someone tell me the name of what I'm trying to do, and direct
> > me to the right reading? I want to insert some text if a certain field is
> > not null. I also want to insert the contents of that field in the
> > conditional text, i.e.:
> >
> > ...renew extensions of credit to the BKBORROWER[ and BKBORROWER2, each
> > singly or collectively].
> >
> > Thanks in advance.
> > Sandi
> >
> >
> >

SandiV
Fri Jul 11 10:00:26 PDT 2008

Sorry to be dense: but where does this go?

I've got

Private Sub btnCancel_Click()
Me.Hide
ActiveDocument.Close wdDoNotSaveChanges
End Sub

Private Sub btnOK_Click()
On Error Resume Next
Dim oRng As Word.Range

'Populating Guarantor bookmarks:

Set oRng = ActiveDocument.Bookmarks("bkGName").Range
oRng.Text = Me.txtGName
ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
(etcetera)

Me.Hide
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"

End Sub

If I try to include it under the OK button, I get "expected end sub" error.

This is the code I modified from yours, so far no decompile or run errors;
but the bkBorrowerName does not populate in the document.

Private Sub InsertBKBorrower()
Dim myRange As Range
With ActiveDocument
If .Bookmarks.Exists("bkBorrowerName") Then
myRange = .Bookmarks("bkBorrowerName").Range
myRange.Text = fcnBuildBKBorrower
.Bookmarks.Add "BkBorrowerName", myRange
End If
End With
End Sub

Private Function fcnBuildBKBorrower() As String
Dim Temp As String
Temp = txtBorrower1.Value
If Len(txtBorrower2.Value) > 0 Then
Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
and any reference to 'Borrower' singly or 'Borrowers' collectively means each
as well of them in their individual, and joint and several capacities."
End If
fcnBuildBKBorrower = Temp
End Function

Doug
Fri Jul 11 14:08:41 PDT 2008

If you only dable in vba every six months or so, I would suggest that you
take the simpler approach that I suggested.

--
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

"Sandi V" <SandiV@discussions.microsoft.com> wrote in message
news:2E2285FA-0AA0-4651-86FF-8271C779CD9D@microsoft.com...
> Sorry to be dense: but where does this go?
>
> I've got
>
> Private Sub btnCancel_Click()
> Me.Hide
> ActiveDocument.Close wdDoNotSaveChanges
> End Sub
>
> Private Sub btnOK_Click()
> On Error Resume Next
> Dim oRng As Word.Range
>
> 'Populating Guarantor bookmarks:
>
> Set oRng = ActiveDocument.Bookmarks("bkGName").Range
> oRng.Text = Me.txtGName
> ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
> Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
> (etcetera)
>
> Me.Hide
> ActiveDocument.PrintPreview
> ActiveDocument.ClosePrintPreview
> Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"
>
> End Sub
>
> If I try to include it under the OK button, I get "expected end sub"
> error.
>
> This is the code I modified from yours, so far no decompile or run errors;
> but the bkBorrowerName does not populate in the document.
>
> Private Sub InsertBKBorrower()
> Dim myRange As Range
> With ActiveDocument
> If .Bookmarks.Exists("bkBorrowerName") Then
> myRange = .Bookmarks("bkBorrowerName").Range
> myRange.Text = fcnBuildBKBorrower
> .Bookmarks.Add "BkBorrowerName", myRange
> End If
> End With
> End Sub
>
> Private Function fcnBuildBKBorrower() As String
> Dim Temp As String
> Temp = txtBorrower1.Value
> If Len(txtBorrower2.Value) > 0 Then
> Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
> and any reference to 'Borrower' singly or 'Borrowers' collectively means
> each
> as well of them in their individual, and joint and several capacities."
> End If
> fcnBuildBKBorrower = Temp
> End Function
>
>

gordon(dot)bentleymix(at)gmail(dot)com>
Sun Jul 13 16:01:01 PDT 2008

Sandi,

I agree with Doug. However, I think you could probably make my code work if
you just made a call to the "InsertBKBorrower" procedure in your OK button
code. (In fact, I'd recommend making your code as modular as possible anyway.
For example, the code for inserting the Guarantor info could be placed in a
separate sub, which is then called in the OK button code.)

I'd also be interested to know what's going on in in the InsertBKBorrower
code that's making it fail. Have you tried inserting a break point at the
start of this sub and then stepping the code to see what's going on? If all
else fails you can send me the template and I'll take a look.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


"Doug Robbins - Word MVP" wrote:

> If you only dable in vba every six months or so, I would suggest that you
> take the simpler approach that I suggested.
>
> --
> 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
>
> "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> news:2E2285FA-0AA0-4651-86FF-8271C779CD9D@microsoft.com...
> > Sorry to be dense: but where does this go?
> >
> > I've got
> >
> > Private Sub btnCancel_Click()
> > Me.Hide
> > ActiveDocument.Close wdDoNotSaveChanges
> > End Sub
> >
> > Private Sub btnOK_Click()
> > On Error Resume Next
> > Dim oRng As Word.Range
> >
> > 'Populating Guarantor bookmarks:
> >
> > Set oRng = ActiveDocument.Bookmarks("bkGName").Range
> > oRng.Text = Me.txtGName
> > ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
> > (etcetera)
> >
> > Me.Hide
> > ActiveDocument.PrintPreview
> > ActiveDocument.ClosePrintPreview
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"
> >
> > End Sub
> >
> > If I try to include it under the OK button, I get "expected end sub"
> > error.
> >
> > This is the code I modified from yours, so far no decompile or run errors;
> > but the bkBorrowerName does not populate in the document.
> >
> > Private Sub InsertBKBorrower()
> > Dim myRange As Range
> > With ActiveDocument
> > If .Bookmarks.Exists("bkBorrowerName") Then
> > myRange = .Bookmarks("bkBorrowerName").Range
> > myRange.Text = fcnBuildBKBorrower
> > .Bookmarks.Add "BkBorrowerName", myRange
> > End If
> > End With
> > End Sub
> >
> > Private Function fcnBuildBKBorrower() As String
> > Dim Temp As String
> > Temp = txtBorrower1.Value
> > If Len(txtBorrower2.Value) > 0 Then
> > Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
> > and any reference to 'Borrower' singly or 'Borrowers' collectively means
> > each
> > as well of them in their individual, and joint and several capacities."
> > End If
> > fcnBuildBKBorrower = Temp
> > End Function
> >
> >
>
>
>

SandiV
Mon Jul 21 09:47:01 PDT 2008

Many thanks to you both! Sorry for the late response - I always forget to
click "Notify me of replies". I was coming back to post the solution for
posterity. I went with Doug's method; mainly -- but got some good info from
both of you & learned some new things! Here's what worked out the end:

With ActiveDocument

If Len(txtBorrower2.Text) > 0 Then


Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
oRng.Text = Me.txtBorrower1 & " and " & Me.txtBorrower2
ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"

With .Variables("varBorrower")
.Value = ", the Borrowers. 'Borrower' and any reference to
'Borrower' singly or 'Borrowers' collectively means each as well of them in
their individual, and joint and several capacities."
End With

Else

Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
oRng.Text = Me.txtBorrower1
ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"

With .Variables("varBorrower")
.Value = ", the Borrower."
End With
End If

.Range.Fields.Update

End With


Thanks very much for your help!

"Gordon Bentley-Mix" wrote:

> Sandi,
>
> I agree with Doug. However, I think you could probably make my code work if
> you just made a call to the "InsertBKBorrower" procedure in your OK button
> code. (In fact, I'd recommend making your code as modular as possible anyway.
> For example, the code for inserting the Guarantor info could be placed in a
> separate sub, which is then called in the OK button code.)
>
> I'd also be interested to know what's going on in in the InsertBKBorrower
> code that's making it fail. Have you tried inserting a break point at the
> start of this sub and then stepping the code to see what's going on? If all
> else fails you can send me the template and I'll take a look.
> --
> Cheers!
> Gordon
>
> Uninvited email contact will be marked as SPAM and ignored. Please post all
> follow-ups to the newsgroup.
>
>
> "Doug Robbins - Word MVP" wrote:
>
> > If you only dable in vba every six months or so, I would suggest that you
> > take the simpler approach that I suggested.
> >
> > --
> > 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
> >
> > "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> > news:2E2285FA-0AA0-4651-86FF-8271C779CD9D@microsoft.com...
> > > Sorry to be dense: but where does this go?
> > >
> > > I've got
> > >
> > > Private Sub btnCancel_Click()
> > > Me.Hide
> > > ActiveDocument.Close wdDoNotSaveChanges
> > > End Sub
> > >
> > > Private Sub btnOK_Click()
> > > On Error Resume Next
> > > Dim oRng As Word.Range
> > >
> > > 'Populating Guarantor bookmarks:
> > >
> > > Set oRng = ActiveDocument.Bookmarks("bkGName").Range
> > > oRng.Text = Me.txtGName
> > > ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
> > > Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
> > > (etcetera)
> > >
> > > Me.Hide
> > > ActiveDocument.PrintPreview
> > > ActiveDocument.ClosePrintPreview
> > > Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"
> > >
> > > End Sub
> > >
> > > If I try to include it under the OK button, I get "expected end sub"
> > > error.
> > >
> > > This is the code I modified from yours, so far no decompile or run errors;
> > > but the bkBorrowerName does not populate in the document.
> > >
> > > Private Sub InsertBKBorrower()
> > > Dim myRange As Range
> > > With ActiveDocument
> > > If .Bookmarks.Exists("bkBorrowerName") Then
> > > myRange = .Bookmarks("bkBorrowerName").Range
> > > myRange.Text = fcnBuildBKBorrower
> > > .Bookmarks.Add "BkBorrowerName", myRange
> > > End If
> > > End With
> > > End Sub
> > >
> > > Private Function fcnBuildBKBorrower() As String
> > > Dim Temp As String
> > > Temp = txtBorrower1.Value
> > > If Len(txtBorrower2.Value) > 0 Then
> > > Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
> > > and any reference to 'Borrower' singly or 'Borrowers' collectively means
> > > each
> > > as well of them in their individual, and joint and several capacities."
> > > End If
> > > fcnBuildBKBorrower = Temp
> > > End Function
> > >
> > >
> >
> >
> >

gordon(dot)bentleymix(at)gmail(dot)com>
Mon Jul 21 15:48:00 PDT 2008

Looks good Sandi, but I have a question and a couple of tips for you.

Question:
In Re-
> Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"

Is there a particular reason for moving the Selection to the Bookmark after
writing the text into it and then re-inserting it? If so, you can do this
outside of the If statement to save repeating the code. And if not, I'd
recommend taking it out, as it will just slow things down and make the screen
jump around annoyingly. (That's the first tip. <g>)

More Tips:
In Re-
> oRng.Text = Me.txtBorrower1 & " and " & Me.txtBorrower2
and
> oRng.Text = Me.txtBorrower1

It's just good practice to explicitly state the property of an object that
you're working with rather than just relying on the default property. It
works OK in the examples above because the default property of a TextBox is
.Value, but this can be risky if MS desides to change the default (not
unheard of) and then your code breaks without warning. It also gets you in
the habit of thinking about which property you want to use so you remember to
specify the correct property when you want something other than the default.

This good practice would result in:
oRng.Text = txtBorrower1.Value & " and " & txtBorrower2.Value
and
oRng.Text = txtBorrower1.Value

(Note that the use of 'Me.' is also not required - although it does make
finding a particular Control on a UserForm easier.)

In Re-
> With .Variables("varBorrower")
***
> End With

The use of a With statement is not required in this instance as you are only
working with one property of the "varBorrower" variable - the .Value
property. (Well done on specifying the property in this instance though.)
Accordingly, you could just use:

.Variables("varBorrower").Value = [etc.]

Generally-
I see a lot of repetition in you code, which could be eliminated quite
easily, as follows:

Sub SandiRevised()
Dim oRng As Range
Dim myBookmarkText As String
Dim myVariableValue As String
With ActiveDocument
If Len(txtBorrower2.Text) > 0 Then
myBookmarkText = txtBorrower1.Value & " and " & txtBorrower2.Value
myVariableValue = ", the Borrowers. 'Borrower' and any reference
to 'Borrower'" _
& " singly or 'Borrowers' collectively means each as well of
them in their" _
& " individual, and joint and several capacities."
Else
myBookmarkText = txtBorrower1.Value
myVariableValue = ", the Borrower."
End If
Set oRng = .Bookmarks("bkBorrower1").Range
oRng.Text = myBookmarkText
.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
.Variables("varBorrower").Value = myVariableValue
.Range.Fields.Update
End With
End Sub

I'm not quite sure why you're using both a bookmark and a document variable,
but I'm assuming that you (possibly?) have several 'bkBorrower' bookmarks
that need only display the borrower names, and then one particular point that
requires the full definition of "Borrower" - which is displayed through the
use of a DOCVARIABLE field. You could probably use just one or the other
exclusively, and I believe there may be certain advantages to the use of
fields (e.g. REF fields?). Otherwise, if you're happy, I'm happy! :-D
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


"Sandi V" wrote:

> Many thanks to you both! Sorry for the late response - I always forget to
> click "Notify me of replies". I was coming back to post the solution for
> posterity. I went with Doug's method; mainly -- but got some good info from
> both of you & learned some new things! Here's what worked out the end:
>
> With ActiveDocument
>
> If Len(txtBorrower2.Text) > 0 Then
>
>
> Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
> oRng.Text = Me.txtBorrower1 & " and " & Me.txtBorrower2
> ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
> Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
>
> With .Variables("varBorrower")
> .Value = ", the Borrowers. 'Borrower' and any reference to
> 'Borrower' singly or 'Borrowers' collectively means each as well of them in
> their individual, and joint and several capacities."
> End With
>
> Else
>
> Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
> oRng.Text = Me.txtBorrower1
> ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
> Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
>
> With .Variables("varBorrower")
> .Value = ", the Borrower."
> End With
> End If
>
> .Range.Fields.Update
>
> End With
>
>
> Thanks very much for your help!
>
> "Gordon Bentley-Mix" wrote:
>
> > Sandi,
> >
> > I agree with Doug. However, I think you could probably make my code work if
> > you just made a call to the "InsertBKBorrower" procedure in your OK button
> > code. (In fact, I'd recommend making your code as modular as possible anyway.
> > For example, the code for inserting the Guarantor info could be placed in a
> > separate sub, which is then called in the OK button code.)
> >
> > I'd also be interested to know what's going on in in the InsertBKBorrower
> > code that's making it fail. Have you tried inserting a break point at the
> > start of this sub and then stepping the code to see what's going on? If all
> > else fails you can send me the template and I'll take a look.
> > --
> > Cheers!
> > Gordon
> >
> > Uninvited email contact will be marked as SPAM and ignored. Please post all
> > follow-ups to the newsgroup.
> >
> >
> > "Doug Robbins - Word MVP" wrote:
> >
> > > If you only dable in vba every six months or so, I would suggest that you
> > > take the simpler approach that I suggested.
> > >
> > > --
> > > 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
> > >
> > > "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> > > news:2E2285FA-0AA0-4651-86FF-8271C779CD9D@microsoft.com...
> > > > Sorry to be dense: but where does this go?
> > > >
> > > > I've got
> > > >
> > > > Private Sub btnCancel_Click()
> > > > Me.Hide
> > > > ActiveDocument.Close wdDoNotSaveChanges
> > > > End Sub
> > > >
> > > > Private Sub btnOK_Click()
> > > > On Error Resume Next
> > > > Dim oRng As Word.Range
> > > >
> > > > 'Populating Guarantor bookmarks:
> > > >
> > > > Set oRng = ActiveDocument.Bookmarks("bkGName").Range
> > > > oRng.Text = Me.txtGName
> > > > ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
> > > > Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
> > > > (etcetera)
> > > >
> > > > Me.Hide
> > > > ActiveDocument.PrintPreview
> > > > ActiveDocument.ClosePrintPreview
> > > > Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"
> > > >
> > > > End Sub
> > > >
> > > > If I try to include it under the OK button, I get "expected end sub"
> > > > error.
> > > >
> > > > This is the code I modified from yours, so far no decompile or run errors;
> > > > but the bkBorrowerName does not populate in the document.
> > > >
> > > > Private Sub InsertBKBorrower()
> > > > Dim myRange As Range
> > > > With ActiveDocument
> > > > If .Bookmarks.Exists("bkBorrowerName") Then
> > > > myRange = .Bookmarks("bkBorrowerName").Range
> > > > myRange.Text = fcnBuildBKBorrower
> > > > .Bookmarks.Add "BkBorrowerName", myRange
> > > > End If
> > > > End With
> > > > End Sub
> > > >
> > > > Private Function fcnBuildBKBorrower() As String
> > > > Dim Temp As String
> > > > Temp = txtBorrower1.Value
> > > > If Len(txtBorrower2.Value) > 0 Then
> > > > Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
> > > > and any reference to 'Borrower' singly or 'Borrowers' collectively means
> > > > each
> > > > as well of them in their individual, and joint and several capacities."
> > > > End If
> > > > fcnBuildBKBorrower = Temp
> > > > End Function
> > > >
> > > >
> > >
> > >
> > >

gordon(dot)bentleymix(at)gmail(dot)com>
Mon Jul 21 17:38:02 PDT 2008

One more tip:

You might want to check for the existence of a bookmark before trying to
write into it. For example:

With ActiveDocument
[SNIP]
If .Bookmarks.Exists("bkBorrower1") Then
Set oRng = .Bookmarks("bkBorrower1").Range
oRng.Text = myBookmarkText
.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
'*** Next line optional ***
' Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
End If
[SNIP]
End With

The reason for doing this is that if the bookmark isn't there, Word doesn't
throw an error; it just stops executing of the code. You can also add an
'Else' condition to pop a message box to indicate that the bookmark can't be
found.

And finally...

If you do this sort of thing a lot - inserting text into a bookmark range -
you might consider writing a procedure that accepts arguments to do this. It
would look something like this:

Private Sub InsertBookmarkText(BookmarkName as String, BookmarkText as String)
With ActiveDocument
If .Bookmarks.Exists(BookmarkName) Then
Set oRng = .Bookmarks(BookmarkName).Range
oRng.Text = BookmarkText
.Bookmarks.Add Name:=BookmarkName, Range:=oRng
'*** Next line optional ***
' Selection.GoTo What:=wdGoToBookmark, Name:=BookmarkName
End If
End With

Then you would call this procedure like this (for example):

Sub SandiRevised()
Dim oRng As Range
Dim myBookmarkText As String
Dim myVariableValue As String
With ActiveDocument
If Len(txtBorrower2.Text) > 0 Then
*** Set value of myBookmarkText, etc. ***
Else
*** Set different value of myBookmarkText, etc. ***
End If
InsertBookmarkText "bkBorrower1", myBookmarkText
*** Do the rest of the stuff that needs to be done ***
End With
End Sub

This will save you quite a lot of typing (or copying and pasting as the case
may be).
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

SandiV
Tue Jul 22 12:38:16 PDT 2008

Thanks very much for your feedback & tips! I really appreciate your time.
First:

> In Re-
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
>
> Is there a particular reason for moving the Selection to the Bookmark after
> writing the text into it and then re-inserting it?

Nope! None at all! I've been copying, pasting & modifying from my original
VBA class materials, which had this line (for a purpose or not, I don't
know). I commented all of those out with no detriment & will delete them
soon. The only one I need to keep is Selection.GoTo What:=wdGoToBookmark,
Name:="bkStartHere"; which lands the cursor at the place where user starts to
type.

Two:

> This good practice would result in:
> oRng.Text = txtBorrower1.Value & " and " & txtBorrower2.Value
> and
> oRng.Text = txtBorrower1.Value

Gotcha. Tested and good. I asked myself "what else is there besides
.value?" Wow. So much to learn.

Three:

> The use of a With statement is not required in this instance as you are only
> working with one property of the "varBorrower" variable - the .Value
> property.

I understand that in theory. Am having trouble making it work. I'll post
separately if I can't figure it out.

Four:

> Generally-
> I see a lot of repetition in you code, which could be eliminated quite
> easily, as follows:

Ditto above. I know, I'll try!

Thanks again very much! I see your second post following this one; I will
f/u on that tomorrow.

Sandi


"Gordon Bentley-Mix" wrote:

> Looks good Sandi, but I have a question and a couple of tips for you.
>
> Question:
> In Re-
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
>
> Is there a particular reason for moving the Selection to the Bookmark after
> writing the text into it and then re-inserting it? If so, you can do this
> outside of the If statement to save repeating the code. And if not, I'd
> recommend taking it out, as it will just slow things down and make the screen
> jump around annoyingly. (That's the first tip. <g>)
>
> More Tips:
> In Re-
> > oRng.Text = Me.txtBorrower1 & " and " & Me.txtBorrower2
> and
> > oRng.Text = Me.txtBorrower1
>
> It's just good practice to explicitly state the property of an object that
> you're working with rather than just relying on the default property. It
> works OK in the examples above because the default property of a TextBox is
> .Value, but this can be risky if MS desides to change the default (not
> unheard of) and then your code breaks without warning. It also gets you in
> the habit of thinking about which property you want to use so you remember to
> specify the correct property when you want something other than the default.
>
> This good practice would result in:
> oRng.Text = txtBorrower1.Value & " and " & txtBorrower2.Value
> and
> oRng.Text = txtBorrower1.Value
>
> (Note that the use of 'Me.' is also not required - although it does make
> finding a particular Control on a UserForm easier.)
>
> In Re-
> > With .Variables("varBorrower")
> ***
> > End With
>
> The use of a With statement is not required in this instance as you are only
> working with one property of the "varBorrower" variable - the .Value
> property. (Well done on specifying the property in this instance though.)
> Accordingly, you could just use:
>
> .Variables("varBorrower").Value = [etc.]
>
> Generally-
> I see a lot of repetition in you code, which could be eliminated quite
> easily, as follows:
>
> Sub SandiRevised()
> Dim oRng As Range
> Dim myBookmarkText As String
> Dim myVariableValue As String
> With ActiveDocument
> If Len(txtBorrower2.Text) > 0 Then
> myBookmarkText = txtBorrower1.Value & " and " & txtBorrower2.Value
> myVariableValue = ", the Borrowers. 'Borrower' and any reference
> to 'Borrower'" _
> & " singly or 'Borrowers' collectively means each as well of
> them in their" _
> & " individual, and joint and several capacities."
> Else
> myBookmarkText = txtBorrower1.Value
> myVariableValue = ", the Borrower."
> End If
> Set oRng = .Bookmarks("bkBorrower1").Range
> oRng.Text = myBookmarkText
> .Bookmarks.Add Name:="bkBorrower1", Range:=oRng
> Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
> .Variables("varBorrower").Value = myVariableValue
> .Range.Fields.Update
> End With
> End Sub
>
> I'm not quite sure why you're using both a bookmark and a document variable,
> but I'm assuming that you (possibly?) have several 'bkBorrower' bookmarks
> that need only display the borrower names, and then one particular point that
> requires the full definition of "Borrower" - which is displayed through the
> use of a DOCVARIABLE field. You could probably use just one or the other
> exclusively, and I believe there may be certain advantages to the use of
> fields (e.g. REF fields?). Otherwise, if you're happy, I'm happy! :-D
> --
> Cheers!
> Gordon
>
> Uninvited email contact will be marked as SPAM and ignored. Please post all
> follow-ups to the newsgroup.
>
>
> "Sandi V" wrote:
>
> > Many thanks to you both! Sorry for the late response - I always forget to
> > click "Notify me of replies". I was coming back to post the solution for
> > posterity. I went with Doug's method; mainly -- but got some good info from
> > both of you & learned some new things! Here's what worked out the end:
> >
> > With ActiveDocument
> >
> > If Len(txtBorrower2.Text) > 0 Then
> >
> >
> > Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
> > oRng.Text = Me.txtBorrower1 & " and " & Me.txtBorrower2
> > ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
> >
> > With .Variables("varBorrower")
> > .Value = ", the Borrowers. 'Borrower' and any reference to
> > 'Borrower' singly or 'Borrowers' collectively means each as well of them in
> > their individual, and joint and several capacities."
> > End With
> >
> > Else
> >
> > Set oRng = ActiveDocument.Bookmarks("bkBorrower1").Range
> > oRng.Text = Me.txtBorrower1
> > ActiveDocument.Bookmarks.Add Name:="bkBorrower1", Range:=oRng
> > Selection.GoTo What:=wdGoToBookmark, Name:="bkBorrower1"
> >
> > With .Variables("varBorrower")
> > .Value = ", the Borrower."
> > End With
> > End If
> >
> > .Range.Fields.Update
> >
> > End With
> >
> >
> > Thanks very much for your help!
> >
> > "Gordon Bentley-Mix" wrote:
> >
> > > Sandi,
> > >
> > > I agree with Doug. However, I think you could probably make my code work if
> > > you just made a call to the "InsertBKBorrower" procedure in your OK button
> > > code. (In fact, I'd recommend making your code as modular as possible anyway.
> > > For example, the code for inserting the Guarantor info could be placed in a
> > > separate sub, which is then called in the OK button code.)
> > >
> > > I'd also be interested to know what's going on in in the InsertBKBorrower
> > > code that's making it fail. Have you tried inserting a break point at the
> > > start of this sub and then stepping the code to see what's going on? If all
> > > else fails you can send me the template and I'll take a look.
> > > --
> > > Cheers!
> > > Gordon
> > >
> > > Uninvited email contact will be marked as SPAM and ignored. Please post all
> > > follow-ups to the newsgroup.
> > >
> > >
> > > "Doug Robbins - Word MVP" wrote:
> > >
> > > > If you only dable in vba every six months or so, I would suggest that you
> > > > take the simpler approach that I suggested.
> > > >
> > > > --
> > > > 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
> > > >
> > > > "Sandi V" <SandiV@discussions.microsoft.com> wrote in message
> > > > news:2E2285FA-0AA0-4651-86FF-8271C779CD9D@microsoft.com...
> > > > > Sorry to be dense: but where does this go?
> > > > >
> > > > > I've got
> > > > >
> > > > > Private Sub btnCancel_Click()
> > > > > Me.Hide
> > > > > ActiveDocument.Close wdDoNotSaveChanges
> > > > > End Sub
> > > > >
> > > > > Private Sub btnOK_Click()
> > > > > On Error Resume Next
> > > > > Dim oRng As Word.Range
> > > > >
> > > > > 'Populating Guarantor bookmarks:
> > > > >
> > > > > Set oRng = ActiveDocument.Bookmarks("bkGName").Range
> > > > > oRng.Text = Me.txtGName
> > > > > ActiveDocument.Bookmarks.Add Name:="bkGName", Range:=oRng
> > > > > Selection.GoTo What:=wdGoToBookmark, Name:="bkGName"
> > > > > (etcetera)
> > > > >
> > > > > Me.Hide
> > > > > ActiveDocument.PrintPreview
> > > > > ActiveDocument.ClosePrintPreview
> > > > > Selection.GoTo What:=wdGoToBookmark, Name:="bkStartHere"
> > > > >
> > > > > End Sub
> > > > >
> > > > > If I try to include it under the OK button, I get "expected end sub"
> > > > > error.
> > > > >
> > > > > This is the code I modified from yours, so far no decompile or run errors;
> > > > > but the bkBorrowerName does not populate in the document.
> > > > >
> > > > > Private Sub InsertBKBorrower()
> > > > > Dim myRange As Range
> > > > > With ActiveDocument
> > > > > If .Bookmarks.Exists("bkBorrowerName") Then
> > > > > myRange = .Bookmarks("bkBorrowerName").Range
> > > > > myRange.Text = fcnBuildBKBorrower
> > > > > .Bookmarks.Add "BkBorrowerName", myRange
> > > > > End If
> > > > > End With
> > > > > End Sub
> > > > >
> > > > > Private Function fcnBuildBKBorrower() As String
> > > > > Dim Temp As String
> > > > > Temp = txtBorrower1.Value
> > > > > If Len(txtBorrower2.Value) > 0 Then
> > > > > Temp = Temp & " and " & txtBorrower2.Value & ", each, a 'Borrower'
> > > > > and any reference to 'Borrower' singly or 'Borrowers' collectively means
> > > > > each
> > > > > as well of them in their individual, and joint and several capacities."
> > > > > End If
> > > > > fcnBuildBKBorrower = Temp
> > > > > End Function
> > > > >
> > > > >
> > > >
> > > >
> > > >

gordon(dot)bentleymix(at)gmail(dot)com>
Tue Jul 22 14:50:00 PDT 2008

Sandi,

Another tip that I hit on myself yesterday:

If there's a chance that the user might enter leading or trailing spaces
into a TextBox, you can use the Trim function to remove these. This could
come into play in situations like

oRng.Text = txtBorrower1.Value & " and " & txtBorrower2.Value

where leading or trailing spaces would stuff up the "flow" of the text
inserted into the specified range. For example, if the user (accidentally of
course!) put 3 trailing spaces after the first borrower's name and 3 leading
spaces before the second, your document would look like this:

... Joe Bloggs and Fred Dagg ...

Not an especially desirable result.

To avoid this use:

oRng.Text = Trim(txtBorrower1.Value) & " and " &