Solved: Text from mutliline txtbox to merged cell

[pcsparky]

I want the user to be able to use the enter button in a multiline textbox, which works fine.

But when I send that data to a merged cell, Excel doesn't recognise the use of the enter button for a new line and creates a small square. The font is Arial but others produce same result.

I know that you can use Alt+Enter in a cell but is there any way around this when sending the data?

Basically I want the user to be able to type grammatically and be shown accordingly in the cell.

I have also tried sending the text to a sheet textbox but that displays the line break as the new paragraph symbol.

[Paul_Hossler]

Got a small sample workbook?

Paul

[lucas]

Here's an example that I threw together. I see no squares in it.

Maybe it's how you set the range value.

[mikerickson]

Have you tried replacing Chr(10) with Chr(13) or vice versa?

[pcsparky]

Thanks guys. The example xlsm wb didn't work as I could still see the squares. But I did use it to play around with alternatives.

I did manage to sort it with a slight alteration to MikeRickson's suggestion. Replacing Chr(13) with Chr(10) produces two line breaks but does get rid of the squares.

This does the trick though:

Range("A1") = Replace(TextBox1.Text, Chr(13), "")

[GTO]

Hi pcsparky,

I see that you solved it, nicely done

I believe that (leastwise in WIN) a hard return produced by using the Enter key whilst in a multiline textbox, produces a combination Chr(13) + Chr(10).

Using Steve's example wb:
[vba]Private Sub CommandButton1_Click()
Range("A1") = Replace(TextBox1.Value, Chr(13), "")
Unload Me
End Sub[/vba]
If you put a breakpoint at the top of the procedure, you will see two little squares (at ea hard return) while holding the cursor over Textbox1.Value; the first is the Chr(13), the second of course, the Chr(10).

Have a great day :-)

Mark