Do...Until Loop not doing what I expected

[Kristi]

I have a userform with a text box in which multiple lines can be entered. It
is a letter template and the text box is to indicate "copies to" at the
bottom of the letter. If there is more than one person to be copied
(separated by Enter key), I want those on lines 2+ to be indented in my
letter.

Below is my code. It works fine if there are two or less people being
copied. If there are three or more, it puts all the tabs on the last line.
It is as if it is skipping the "Selection.MoveUp Unit:=wdParagraph, Count:=1"
line in the loop statement. The value of the variable NumCopies is assigned
previously to equal the linecount of the text box. Why is it putting all the
tabs on the last line?

Using Word 2002. Thank you.

If NumCopies > 1 Then
Selection.HomeKey Unit:=wdLine
Selection.TypeText Text:=vbTab
NumCopies = NumCopies - 2
Do Until NumCopies = 0
Selection.MoveUp Unit:=wdParagraph, Count:=1
Selection.HomeKey Unit:=wdLine
Selection.TypeText Text:=vbTab
NumCopies = NumCopies - 1
Loop
End If

 

[Jay Freedman]

Hi Kristi,

The problem is in the MoveUp statement. If the Selection isn't at the
beginning of the line when you execute that statement (which it won't
be because you just inserted a tab before entering the loop and each
time around the loop), MoveUp moves the Selection to the start of that
line, not to the previous line. (Yeah, it's stupid, but we're stuck
with it...) Then the HomeKey statement does nothing, and the next tab
goes in to the left of the previous tab.

A solution is to swap the MoveUp and HomeKey lines. That way, the
Selection is always at the start of the line when MoveUp executes, and
it really does move up.

A better solution is to change the whole operation. Declare a string
variable, and use it to build up the list of names from the text box,
placing vbCr & vbTab between the names. When the string is complete,
just insert it in the document.

As a general rule, avoid manipulating the Selection as much as
possible. It will help keep you sane.

 

[Kristi]

Thanks, Jay. Would it be too much to ask for you to give me sample code for
the better solution you mentioned? I'm very much a newbie. I understand the
actions you describe, but don't know how to put it all together. Would you
use a For each...Next statement or something? Sorry, I'm pretty clueless.

 

[Jay Freedman]

Hi Kristi,

The reason I wasn't more specific before is that I wasn't sure how you
were transferring the names from the textbox to the document -- the
part before the code you posted. I'll give you a sort of generic
solution, and if it doesn't fit then come back and explain.

If you examine the textbox's .Text property in the debugger after
you've typed a few names in and hit Enter after each one, you'll find
that each Enter results in two characters, Chr(13) and Chr(10), which
are represented by the named constants vbCr and vbLf. If you insert
this string into the document, Word silently drops the vbLf and
considers the vbCr to be a paragraph mark.

What I suggest is to change each substring vbCr & vbLf into the
substring vbCr & vbTab. (The & operator sticks two strings together,
formally called "concatenation"). In Word 2000 and later, this is
really easy to do with the Replace function (Word 97 doesn't have it).
You feed in the full string, the substring to look for, and the
replacement substring; the function does all the replacements in the
string and returns the result.

For a demo userform with a single multiline textbox named TextBox1 and
a single button named CommandButton1, and simply sticking the string
at the end of the active document, the code looks like this:

Private Sub CommandButton1_Click()
Dim strTemp As String

strTemp = TextBox1.Text

' if there's nothing in the box,
' just exit the userform
If Len(Trim(strTemp)) > 0 Then
' replace any para plus line feed (ASCII 13 + 10)
' with para plus tab
strTemp = Replace(strTemp, vbCr & vbLf, vbCr & vbTab)

' insert at end of doc
ActiveDocument.Range.InsertAfter strTemp
End If

Unload Me
End Sub

 

[Kristi]

It worked perfectly. Thank you so much!
Kristi

Hi Kristi,

The reason I wasn't more specific before is that I wasn't sure how you
were transferring the names from the textbox to the document -- the
part before the code you posted. I'll give you a sort of generic
solution, and if it doesn't fit then come back and explain.

If you examine the textbox's .Text property in the debugger after
you've typed a few names in and hit Enter after each one, you'll find
that each Enter results in two characters, Chr(13) and Chr(10), which
are represented by the named constants vbCr and vbLf. If you insert
this string into the document, Word silently drops the vbLf and
considers the vbCr to be a paragraph mark.

What I suggest is to change each substring vbCr & vbLf into the
substring vbCr & vbTab. (The & operator sticks two strings together,
formally called "concatenation"). In Word 2000 and later, this is
really easy to do with the Replace function (Word 97 doesn't have it).
You feed in the full string, the substring to look for, and the
replacement substring; the function does all the replacements in the
string and returns the result.

For a demo userform with a single multiline textbox named TextBox1 and
a single button named CommandButton1, and simply sticking the string
at the end of the active document, the code looks like this:

Private Sub CommandButton1_Click()
Dim strTemp As String

strTemp = TextBox1.Text

' if there's nothing in the box,
' just exit the userform
If Len(Trim(strTemp)) > 0 Then
' replace any para plus line feed (ASCII 13 + 10)
' with para plus tab
strTemp = Replace(strTemp, vbCr & vbLf, vbCr & vbTab)

' insert at end of doc
ActiveDocument.Range.InsertAfter strTemp
End If

Unload Me
End Sub