square boxes when using vbcrlf

[Joan]

KBArticle 169972 says:
In Microsoft Excel 97 for Windows, if you use these
constants when you insert text into a text box or into a
cell, a square character may appear in the text box or
cell. This problem occurs if you use any of the following
constants:
vbBack vbCr vbCrLf vbNewLine vbTab

.......You can remove the square character by manually
editing the cell.

______________________________

I have found that this behavior is also in Excel 2000 and
XP. The KB article above is a couple years old. Has
anyone found a solution to removing the boxes
programmatically. I've tried everything I can think of.

Thanks
joan

 

[joan]

Dan, thanks for your reply.

I need to retain the formatting, (the CRs and LFs), so I
cant use your example. The problem is that you can go into
excel and manually remove the little boxes and it
does 'not' disrupt the formatting, it leaves the CRs and
LFs in the text. I can make Excel do just about anything,
in code, but cannot get Excel to let me replace the
squares with nothing.

Joan

-----Original Message-----
Private Sub CommandButton2_Click()
temp = TextBox1.Text
For i = 1 To Len(temp)
a = Mid(temp, i, 1)
If a = vbCrLf Or a = vbCr Or a = vbBack Or a =

vbNewLine Or a = vbTab Or a = vbLf Then

 

[Dan E]

So when you enter a CR or LF into the textbox like

TextBox1.Text = "This" & vbcrlf & "That"

It shows what?
a)
This[]That
b)
This[]
That
c)
This
That

If it is a) put in
Private Sub CommandButton2_Click()
TextBox1.MultiLine = True
End Sub

If it is b) what i put before should work

If it is c) it already works???

Dan E

 

[joan]

I didnt give enough info in my original post, so let me
explain a little better.

I'm saving the text of an email from Outlook to Excel
using VB. My code just grabs the body of the email and
saves it into an excel spreadsheet cell. When you view the
Excel Spreadsheet every CR and LF 'also' has a little box.
You can then manually edit the Excel cell and 'remove' the
little box, and the CRs and LFs are not affected. (And
this is what the KBArticle has to say)

(I'm not interacting with a VB text box).

The code to test for vbcr or lf or etc....removes the CRs
and LFs and the little boxes. However I do not want to
strip the CRs and LFs.

thanks for your help.

joan

-----Original Message-----
So when you enter a CR or LF into the textbox like

TextBox1.Text = "This" & vbcrlf & "That"

It shows what?
a)
This[]That
b)
This[]
That
c)
This
That

If it is a) put in
Private Sub CommandButton2_Click()
TextBox1.MultiLine = True
End Sub

If it is b) what i put before should work

If it is c) it already works???

Dan E

Dan, thanks for your reply.

I need to retain the formatting, (the CRs and LFs), so I
cant use your example. The problem is that you can go into
excel and manually remove the little boxes and it
does 'not' disrupt the formatting, it leaves the CRs and
LFs in the text. I can make Excel do just about anything,
in code, but cannot get Excel to let me replace the
squares with nothing.

Joan

vbNewLine Or a = vbTab Or a = vbLf Then

.

 

[joan]

I didnt give enough info in my original post, so let me
explain a little better.

I'm saving the text of an email from Outlook to Excel
using VB. My code just grabs the body of the email and
saves it into an excel spreadsheet cell. When you view the
Excel Spreadsheet every CR and LF 'also' has a little box.
You can then manually edit the Excel cell and 'remove' the
little box, and the CRs and LFs are not affected. (And
this is what the KBArticle has to say)

(I'm not interacting with a VB text box).

The code to test for vbcr or lf or etc....removes the CRs
and LFs and the little boxes. However I do not want to
strip the CRs and LFs.

thanks for your help.

joan

-----Original Message-----

 

[Oneide]

What do you get when you insert a code like this:

a) ActiveCell = "This" & vbCrLf & "That" ?

or

b) ActiveCell = "This" & vbLf & "That" ?

If the second works right for you, then you need to do:

ActiveCell = Replace( varYourBigText, vbCrLf, vbLf )

Is this what you were looking for?

I didnt give enough info in my original post, so let me
explain a little better.

I'm saving the text of an email from Outlook to Excel
using VB. My code just grabs the body of the email and
saves it into an excel spreadsheet cell. When you view the
Excel Spreadsheet every CR and LF 'also' has a little box.
You can then manually edit the Excel cell and 'remove' the
little box, and the CRs and LFs are not affected. (And
this is what the KBArticle has to say)

(I'm not interacting with a VB text box).

The code to test for vbcr or lf or etc....removes the CRs
and LFs and the little boxes. However I do not want to
strip the CRs and LFs.

thanks for your help.

joan