ExcelBanter - square boxes when using vbcrlf

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - square boxes when using vbcrlf (https://www.excelbanter.com/excel-programming/273472-square-boxes-when-using-vbcrlf.html)

square boxes when using vbcrlf

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

square boxes when using vbcrlf

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
temp = Left(temp, i - 1) & Right(temp, Len
(temp) - i - 1)
End If
Next
TextBox1.Text = temp
End Sub

removes the boxes and adds nothing in their place.
watch out for word wrap

Dan E
"Joan" wrote in message
...
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

.

square boxes when using vbcrlf

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" wrote in message ...
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
temp = Left(temp, i - 1) & Right(temp, Len
(temp) - i - 1)
End If
Next
TextBox1.Text = temp
End Sub

removes the boxes and adds nothing in their place.
watch out for word wrap

Dan E
"Joan" wrote in message
...
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

.

square boxes when using vbcrlf

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

"joan" wrote in message
...
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
temp = Left(temp, i - 1) & Right(temp, Len
(temp) - i - 1)
End If
Next
TextBox1.Text = temp
End Sub

removes the boxes and adds nothing in their place.
watch out for word wrap

Dan E
"Joan" wrote in message
...
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

.

.

square boxes when using vbcrlf

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?

"joan" escreveu na mensagem
...
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-----
On Mon, 4 Aug 2003 at 10:52:41, joan (joan <joan@prism-
inc.org) wrote:
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.

I think you need to set the MultiLine option on the
TextBox to TRUE then
--
Mike
.