Place Contents of MultiLine TextBox (VBA/Word/97)

Topic

New Reply

Is it possible to get the contents of a multiline textbox from a userform to be placed in a table in MS Word as a single line?

I seem to only get mine to arrive as separate lines i.e new paragraphs. Ideally I would like them to be separated by commas but it has me stumped on how to be a single line only when the multiline contents arrive in Word.

TIA, Leigh

Reply | Quote

Replies

Since Office 97 VBA doesn’t have a built-in Replace function (Office 2000 and XP do have it), you can use this simplistic function:

Function ReplaceString(sIn As String, sWhat As String, sBy As String)
Dim intPos As Integer
Dim sResult As String
sResult = sIn
intPos = InStr(sResult, sWhat)
Do While intPos > 0
sResult = Left(sResult, intPos – 1) & sBy & Mid(sResult, intPos + Len(sWhat))
intPos = InStr(sResult, sWhat)
Loop
ReplaceString = sResult
End Function

Now, instead of inserting something like TextBox1.Text into your document, use ReplaceString(TextBox1.Text, vbCrlf, “, “)

Reply | Quote

Thanks Hans, this has put me on the right track but I haven’t moved into second gear!

My only problem now is that the Userform actually has two instances of Multiline textboxes (Attendees list and an Apologies list).

I used your code and got an expected result with the ReplaceString function with the Multiline text from the Attendees textbox. But the second time I called the Function, and referenced a different textbox, it came up with a ‘Compile error: ByRef argument type mismatch’

Now I believe both the textboxes are the same type but cannot understand what I am missing. The code below is part only.

Private Sub cmdOK_Click()
    Dim strAttendees As String, strAgenda As String
    
    frmMinutes.Hide
    
    strAttendees = txtAttendees.Text
    strApologies = txtApologies.Text

    ActiveDocument.Bookmarks("Attendees").Select
    Selection.Text = ReplaceString(strAttendees, vbCrLf, ", ")
    
    ActiveDocument.Bookmarks("Apologies").Select
    Selection.Text = ReplaceString(strApologies, vbCrLf, ", ")
    
    Unload frmMinutes
    
    ActiveDocument.Bookmarks("Start").Select
    
End Sub

And your function of course.

Public Function ReplaceString(sIn As String, sWhat As String, sBy As String)
    Dim intPos As Integer
    Dim sResult As String
    sResult = sIn
    intPos = InStr(sResult, sWhat)
    Do While intPos > 0
        sResult = Left(sResult, intPos - 1) & sBy & Mid(sResult, intPos + Len(sWhat))
        intPos = InStr(sResult, sWhat)
    Loop
    ReplaceString = sResult
End Function 

What should I be looking for, please?

Reply | Quote

Leigh.

Looks like just a typo – you declare these two string variables:

Dim strAttendees As String, strAgenda As String

But then in the code you’ve got:

strApologies = txtApologies.Text

– Since “strApologies” was never declared, it’s getting created at runtime as a Variant type variable; the function is expecting a string but is getting a Variant, causing the type mismatch.

By the way, you can assign the contents of the string variables into the bookmarks in one statement rather than two, and without needing to select them, by using:

ActiveDocument.Bookmarks(“Apologies”).Range.Text = ReplaceString(strApologies, vbCrLf, “, “)

Gary

Reply | Quote