Strange Character

[Stuart]

I have a large TextBox in a Form, which has MultiLine set to True, and
WordWrap enabled.

When I copy the contents of this TextBox to a range in a sheet (where the
range consists of merged cells, and where Wraptext is enabled), I get
strange 'box-shaped'
characters appearing, which seem to represent where a carriage-return (enter
key) was used in the original Textbox.

Can I avoid this in some way (but still allow use of enter key in the
textbox) or, having copied the contents into the sheet range, can I then
remove them with code, please?

Regards.

 

[Gareth Roberts]

Hi Stuart,

To have the "carriage returns" (for one of a better phrase) appear in an
Excel cell it is only necessary to have the line feed (vbLF or CHR10). The
textbox provides both a carriage return and a line feed i.e. vbCRLF or CHR13
& CHR10.

Thus, if you remove the CHR13s you should be ok. I would use something like.

myCell.value = Replace(txtBox.Value, vbCr, "")

to clean out the vbCRs.

I'm not positive you can use Replace with Excel97 so if you need to run this
on Excel 97 you can use WorksheetFunction.Replace instead of Replace.

HTH,
Gareth.

 

[Stuart]

Many thanks. It sometimes does / sometimes does not
work.

from your reply, here's what I'm using:
(in the form code, from within a With construct)
'copy Textbox contents to sheet_range
.Range("B22") = Me.TbFaxMessage.Value
'strip Cr from the range
.Range("B22").Value = Replace _
(.Range("B22").Value, vbCr, "")

Why might this not be consistent, please?

Regards.