none
Pasting from userform into doc with hanging indent adds leading space to second line RRS feed

  • Question

  • I have created a template that uses VBA code to take information entered into a userform and paste that information into a formatted Word 2007 form letter.  When taking information entered into a multiline textbox on the userform and placing into a location (e.g., a bookmark) within the letter with a hanging indent, all the lines after the first have extra leading space in front of the lines, i.e., the lines visually start about four spaces to the right of the hanging indent indicator in the ruler.  In addition, when showing hidden formatting characters in the Word doc, no space (i.e., the dots that indicate a space) is shown, but visually the lines are about four spaces to the right of the hanging indent indicator in the ruler.  For example, I am trying to format an address in the document after the "To:" so that the left of all lines of the address all line up together.  I can accomplish this using a two-column table and just pasting the information into the second column with the "To:" in the first, but our secretaries seem to not understand how to use tables, so I am trying to provide a format that they are more familiar with (we are required to have our secretaries review the letters before they can go out.)  I've tried various combinations of how the text is entered in the multiline textbox in the userform (CTRL-ENTER, SHIFT-ENTER, etc) but none seem to have any affect on the outcome. I've searched the web and cannot find anything on this.  Is this a bug within Word or is there a special technique that i am unfamiliar with.  Any help would be appreciated.
    Tuesday, July 5, 2011 4:34 PM

Answers

  • Hi Gaffer

    Ah, right, UserForms use Chr(10)&Chr(13) in combination (all the permutations that exist in the world <sigh>) - sorry I didn't think about that before. We can tighten things up a bit, I think, by replacing the combination in one step:

    cleanedString = Replace(controlText, Chr(10) & Chr(13) ,Chr(11))

    Before, I was thinking your string could have different characters, depending on whether the user pressend Enter, or Shift+Enter in the multi-line field...


    Cindy Meister, VSTO/Word MVP
    Wednesday, July 6, 2011 4:27 PM
    Moderator

All replies

  • Hi Gaffer

    As I recall, the controls in a Userform can't "do" a Word newline (Shift+Enter : Chr(11)). So you'd need to first replace all Chr(13) and/or Chr(10) with Chr(11) in the string you're retrieving from the multi-line control before passing it to the document. So that would go something liike:

    Dim controlText as String
    Dim cleanedString as String
    cleanedString = Replace(controlText, Chr(13), Chr(11))
    cleanedString = Replace(cleanedString, Chr(10), Chr(11))
    rngTarget.Text = cleanedString


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by gaffer_937 Wednesday, July 6, 2011 2:29 PM
    • Unmarked as answer by gaffer_937 Wednesday, July 6, 2011 2:29 PM
    Tuesday, July 5, 2011 4:50 PM
    Moderator
  • Cindy,

    Thank you for the quick response.  Your suggestion got me very close, however, it added an extra line feed after each line in the string when it was passed to the document.  What I ended up doing is to first replace all of the newline characters (Chr(10)) within the string with line feed characters (Chr(11)), "delete" these line feeds by replacing them with empty strings, then replace all of the carriage returns (Chr(13)) with line feeds.  This maintained the integrity of the hanging indent within the document when passing the string (i.e., placed a line feed at the end of each line within the string versus a carriage return).  The resultant code that worked is below:

    Dim controlText as String
    Dim cleanedString as String
    cleanedString = Replace(controlText, Chr(10),Chr(11))
    cleanedString = Replace(cleanedString, Chr(11),"")
    cleanedString = Replace(cleanedString, Chr(13), Chr(11))
    rngTarget.Text = cleanedString

    Wednesday, July 6, 2011 2:39 PM
  • Hi Gaffer

    Ah, right, UserForms use Chr(10)&Chr(13) in combination (all the permutations that exist in the world <sigh>) - sorry I didn't think about that before. We can tighten things up a bit, I think, by replacing the combination in one step:

    cleanedString = Replace(controlText, Chr(10) & Chr(13) ,Chr(11))

    Before, I was thinking your string could have different characters, depending on whether the user pressend Enter, or Shift+Enter in the multi-line field...


    Cindy Meister, VSTO/Word MVP
    Wednesday, July 6, 2011 4:27 PM
    Moderator