Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line break problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line break problem
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. -- Regards, Tom Ogilvy "Paul" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line break problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line brea
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 "Paul" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line break problem
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 -- Regards, Tom Ogilvy "Paul" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line break problem
Thanks again Tom!
With this information I was able to program the mousedown event. What a fantastic help you are! Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
transferring multiline text from userform to worksheet - line brea
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transferring text from one worksheet to another | Excel Discussion (Misc queries) | |||
Force line break based on text | Excel Discussion (Misc queries) | |||
UserForm; Textbox; Multiline; NonPrintable Characters | Excel Programming | |||
Copying multiline textboxes from userform to worksheet cell | Excel Programming | |||
How to do a line break in a text formula | Excel Programming |