transferring multiline text from userform to worksheet - line break problem

[Paul]

Hi all,

I have set up a program with which information on a userform is
transferred to a worksheet. I have a problem with multiline text.

For the checkbox I have set the multiline text property to "true". A
user adds an address in the textbox that consists of 4 lines. The
following macro transfers the text to a worksheet when the OK button is
pressed (it finds the last cell, picks the line below that (LR) and
prints the text on that row in the third column from the left:

Private Sub CommandButton1_Click()
Set LR = Worksheets("Deposits").Range("A65000").End(xlUp)
LR.Offset(0, 3).Value = TextBox1.Text
TextBox1.Text = ""
UserForm4.Hide
End Sub

This works like a charm, except when I look in the worksheet the text
will look like this:

(name) []
(street) []
(city, state, zip) []
(country)

The "[]" represent the little squares that indicate a line break. For
some reason I just can't seem to find a way to get rid of them.

Is there a property for the cell that will get rid of the squares, or
perhaps a macro?

A second question about this userform: is there also a way to allow a
user to rightclick and paste the address in the textbox (copied from
another application)? If I rightclick now in the textbox, nothing
happens.

Thanks so much for your help!
Paul

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Set LR = Worksheets("Deposits").Range("A65000").End(xlUp)
LR.Offset(0, 3).Value = Replace(TextBox1.Text,chr(13),"")
TextBox1.Text = ""
UserForm4.Hide
End Sub

Click in the textbox and do Ctrl+V

or program the mousedown event.

 

[Paul]

Thanks so much, Tom! That works great!!!

I knew about the CTRL+V but what is the mousedown event and where
exactly do I program it?

Thanks again,
Paul

 

[Guest]

Paul,
This works for me:

LR.Offset(0, 3).Value = Replace(TextBox1.Text, vbCrLf, Chr(10))

Re your second point I don't believe you can paste into a textbox - I
couldn't!

HTH

 

[Tom Ogilvy]

The mousedown event is one of the events for a textbox - like click or
change:

Option Explicit
Public bBlockEvents As Boolean

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
If bBlockEvents Then
bBlockEvents = False
Exit Sub
End If
If Button = 2 Then
MsgBox "Right Click " & bBlockEvents
End If
bBlockEvents = Not bBlockEvents
End Sub

 

[Paul]

Thanks again Tom!
With this information I was able to program the mousedown event.

What a fantastic help you are!
Paul

 

[Paul]

Thanks so much for your reply, both your alternatives work for my first
problem.
And as far as pasting text in a textbox is concerned, with a lot of
programming it is possible after all!
Paul