Line Breaks in VBA

[ccordner]

Hi.

I have a multi-line text box, which the user can type into. the 'EnterKeyBehavior' is set to "true". But when I transfer the contents to a cell, each carriage return is shown as a box (presumably an unrecognised character).

Is there any way around this?

Thanks
Chris

 

[VoG]

Try setting the cell to wrap text.

 

[ccordner]

That doesn't seem to work.

On close inspectoin it appears to be a square box with a question mark in, but when the cell is copied to another sheet and saved as a PDF, it just shows as a plain box.

Thanks
Chris

 

[obiron]

try replacing chr(10) with vbCrLf before pasting the text

Chr(10) is a newline and vbCrLF is a carriage return and a line feed

 

[ccordner]

That's the weird thing.

When I insert chr(10) in manually, to force a line break, it's fine.

But when the user just presses 'Enter' in the text box, it shows that character. If I knew what the character was, then I could perhaps do some sort of "Find & Replace" on the string.

Chris

 

[Hermanito]

end-of-line characters in a cell are something weird in Excel (hey, I'm a poet )

When you copy the text with multiple lines to a decent text editor, and check the ascii-values of the EOL-characters, you would notice they are vbCrLf, thus Char(10)+Char(13) (or the other way around, I always mix them up).
But if you would check for the existence of vbCrLf with Instr function in VBA, it would come back false, no matches found...
So, the text in the cell has in fact only Char(10) (or Char(13), getting mixed up again), but as soon as you copy/paste, Excel magically replaces them to vbCrLf, at least when you paste into an external text-editor...

Try to replace both vbCr and vbLf with vbCrLf...