Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. However, when initialising a
UserForm with this data, each Chr(10) is duplicated. This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. Hoping someone can help me get
rid of the extra ones. Thanks in advance, Dave.

You need to adjust the width of the column. One return is being caused by
the chr(10), and the other return is due to the column width being too narrow
for the data and th eline is wrapping.

"AussieDave" wrote:

Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. However, when initialising a
UserForm with this data, each Chr(10) is duplicated. This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. Hoping someone can help me get
rid of the extra ones. Thanks in advance, Dave.

Thanks Joel, but that's not the case here. Even when a Chr(10) has
been inserted just to get a blank line, the duplication is still
occurring.
Dave

On Jun 30, 3:19*pm, Joel wrote:
You need to adjust the width of the column. *One return is being caused by
the chr(10), and the other return is due to the column width being too narrow
for the data and th eline is wrapping.

Are you gewtting two lines or three lines when you put chr(10) into the form?
You should get two because you start with one Line and putting the chr(10)
creates a second line.

"AussieDave" wrote:

Thanks Joel, but that's not the case here. Even when a Chr(10) has
been inserted just to get a blank line, the duplication is still
occurring.
Dave

On Jun 30, 3:19 pm, Joel wrote:
You need to adjust the width of the column. One return is being caused by
the chr(10), and the other return is due to the column width being too narrow
for the data and th eline is wrapping.

I put a couple of buttons and a textbox on a small userform.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
With Worksheets("Sheet1").Range("b1")
.WrapText = True
.Value = Replace(Me.TextBox1.Value, vbCr, "")
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.MultiLine = True
.WordWrap = True
.EnterKeyBehavior = True
.Value = Worksheets("Sheet1").Range("A1").Value
End With
End Sub



AussieDave wrote:

Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. However, when initialising a
UserForm with this data, each Chr(10) is duplicated. This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. Hoping someone can help me get
rid of the extra ones. Thanks in advance, Dave.

--

Dave Peterson

FYI, Chr(10) is a Linefeed, not a Carriage Return. Use vbLf instead of vbCr.

"Dave Peterson" wrote:

I put a couple of buttons and a textbox on a small userform.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
With Worksheets("Sheet1").Range("b1")
.WrapText = True
.Value = Replace(Me.TextBox1.Value, vbCr, "")
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.MultiLine = True
.WordWrap = True
.EnterKeyBehavior = True
.Value = Worksheets("Sheet1").Range("A1").Value
End With
End Sub



AussieDave wrote:

Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. However, when initialising a
UserForm with this data, each Chr(10) is duplicated. This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. Hoping someone can help me get
rid of the extra ones. Thanks in advance, Dave.

--

Dave Peterson

I want to keep the linefeeds so the text wraps in the cell. I want to remove
the carriage returns so I don't see those little square characters.

That's why I used:
..Value = Replace(Me.TextBox1.Value, vbCr, "")



Charlie wrote:

FYI, Chr(10) is a Linefeed, not a Carriage Return. Use vbLf instead of vbCr.

"Dave Peterson" wrote:

I put a couple of buttons and a textbox on a small userform.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
With Worksheets("Sheet1").Range("b1")
.WrapText = True
.Value = Replace(Me.TextBox1.Value, vbCr, "")
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.MultiLine = True
.WordWrap = True
.EnterKeyBehavior = True
.Value = Worksheets("Sheet1").Range("A1").Value
End With
End Sub



AussieDave wrote:

Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. However, when initialising a
UserForm with this data, each Chr(10) is duplicated. This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. Hoping someone can help me get
rid of the extra ones. Thanks in advance, Dave.

--

Dave Peterson


--

Dave Peterson

Thanks Dave - as usual, you're right on the button, it works
perfectly.

Thanks also to all others who contributed. Dave

On Jul 1, 7:19*am, Dave Peterson wrote:
I want to keep the linefeeds so the text wraps in the cell. *I want to remove
the carriage returns so I don't see those little square characters. *

That's why I used:
.Value = Replace(Me.TextBox1.Value, vbCr, "")





Charlie wrote:

FYI, Chr(10) is a Linefeed, not a Carriage Return. *Use vbLf instead of vbCr.

"Dave Peterson" wrote:

I put a couple of buttons and a textbox on a small userform.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
* * With Worksheets("Sheet1").Range("b1")
* * * * .WrapText = True
* * * * .Value = Replace(Me.TextBox1.Value, vbCr, "")
* * End With
End Sub
Private Sub CommandButton2_Click()
* * Unload Me
End Sub
Private Sub UserForm_Initialize()
* * With Me.TextBox1
* * * * .MultiLine = True
* * * * .WordWrap = True
* * * * .EnterKeyBehavior = True
* * * * .Value = Worksheets("Sheet1").Range("A1").Value
* * End With
End Sub

AussieDavewrote:

Each data cell has multiple lines, delineated by a Chr(10) , which
looks perfectly good on the worksheet. *However, when initialising a
UserForm with this data, each Chr(10) is duplicated. *This only
becomes apparent when I save the data from the Form back to the
worksheet and can then see the superfluous Chr(10) shown as a small
box at the end of each line of data. *Hoping someone can help me get
rid of the extra ones. *Thanks in advance, Dave.

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -