Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User form text boxes and new lines (97 SR-2)

    I am developing a Form to act as an interface for collecting the information required for a lost production report. Some of the information required is free-form text descriptions of events, causes and recommendations. I am using text boxes to collect this information, iIn order to allow the users some freedom with their inputs I have set the EnterKeyBehaviour property to TRUE. This allows the users to start new lines which is the desired behaviour.

    My problem comes when storing the information because whilst new lines are inserted in the correct places in the cell values an extra (unwanted) character is also being added before the new line. I cannot find a way to remove this character, does anyone have an answer for me?

    I have attached a file containing the element of the form that is causing me the trouble. Also in this file is a range showing the difference between my desired and actual ouputs.

    Any help is much appreciated.

    Roger

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User form text boxes and new lines (97 SR-2)

    Use this code instead of your code.
    <pre>Private Sub B_OK_Click()
    Sheets("Log").Range("Output").Value = _
    Application.WorksheetFunction.Substitute( _
    LRInput.TB_Description.Value, vbCr, "")
    Unload Me
    End Sub</pre>

    Excel wraps with a LineFeed character. The text box is adding a linefeed and a carriage return. The Substitute function as listed will replace all the carriage returns.

    Steve

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form text boxes and new lines (97 SR-2)

    Steve,

    Thanks for the speedy response. I knew it was possible because I have a utility that does exactly what I want. I have been trying to fix it for a couple of hours but the closest I got was trying to find vbLf in the text string.

    Once again thanks.

    Roger.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User form text boxes and new lines (97 SR-2)

    Glad I could help.
    Just for clarification:
    The vbLF (ascii 10) is in the string - it what makes excel wrap in a cell (manually using alt-enter puts them in).

    The "box" is from the vbCr (ascii 13). If you checked each char in the string you entered you would find both a vbCr and vbLF wherever the user puts an <enter> in the form.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form text boxes and new lines (97 SR-2)

    I didn't realize the different behaviours Steve. Is the Cr/Lf pair a consequence of the EnterKeyBehavior = True? I think that my wrapped text cell displays always look OK without the EnterKeyBehavior set to true. If this is all correct, it looks like your little routine might come in very handy. It should even be a checkable "feature" in a standard dialog somewhere. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User form text boxes and new lines (97 SR-2)

    The enterkeybehavior only concerns what happens in the text box when you hit enter. If True (and multiline is set true it will create a new line) if false (or multiline is false) the focus will be moved to the next item in tab order

    Ctrl-enter will also wrap (if multiline is true) no matter what the enterkeybehavior

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form text boxes and new lines (97 SR-2)

    Interesting Steve. I never realized that you always ended up with a LF/CR pair from a multiline textbox. I can see from a simple test that the method of injecting a linebreak has no effect - Enter, Ctrl+Enter, Shift+Enter all result in a LF/CR. Your code, using the substitute worksheet function does the conversion job nicely. Still, it begs the question why it's not possible to emulate the behaviour of an Alt+Enter in the formula bar, from within a multiline textbox. ???

    Alan

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User form text boxes and new lines (97 SR-2)

    I stopped trying to figure "WHY" long ago. I just work with what is available <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

Posting Permissions

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