Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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, ", ")

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

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

    And your function of course.
    <pre>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 </pre>


    What should I be looking for, please?

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •