userform textbox: hit enter to start writing in a new line

[sam]

Hi All,

I have a text box in a userform where users enter big sentences. This text
box is designed a little bigger as compared to other text boxes.
What I want is:
If users start inputing the text and they want to write something in a new
line, they can hit enter and start from a new line, Something like microsoft
word, once we hit enter we get on a new line.

I hope I made it clear

Thanks in advance

 

[OssieMac]

Hi Sam,

Set Multiline property to True and
Set EnterKeyBehaviour property to True

 

[sam]

Hey ozziemac, thanks for the help.

I have one issue though, A '?' is displayed in the excel sheet where I hit
enter on the userform textbox:

foreg:

Input in Userforml:
One two three
four five six

Output in Excel sheet cell:
One two three?
four five six

Do you know how to get rid of this '?'

Thanks in advance

 

[OssieMac]

Need to ask a few questions Sam.

I can't replicate the ? character in either xl2002 or xl2007. However, in
xl2002 depending on how the data is transferred to the worksheet, a little
square character can be displayed.

What version of Excel are you using?

How is the text transferred to the worksheet? Do you have the Controlsource
property set to a cell on the worksheet or are you using an AfterUpdate event
to copy it to the worksheet.

In xl2007 both methods work perfectly without displaying any characters on
the worksheet for the Enter.

In xl2002, setting the Controlsource to a cell on the worksheet does not
show any characters on the worksheet for the Enter.

However, in xl2002, if the AfterUpdate event is used to copy the text to the
worksheet, it displays little squares. Using the following formula on the
worksheet I was able to identify that there is both an ASCII 13 and ASCII 10
character for the Enter.
=CODE(MID(B1,5,1)) ('Alter the start parameter for the MID function to
find ASCII code for other characters).

Using the same formula to identify the Enter character in the cell that is
set to the Controlsource only identifies ASCII 10 (Which is the same code
that you get if you use Alt/Enter to force a line feed in the interactive
mode on a worksheet and the ASCII 10 character does not display).

Therefore using the following AfterUpdate event to copy the text to the
worksheet works well. It removes the ASCII 13 and leaves the ASCII 10.

NOTE: A space and underscore at the end of a line is a line break in an
otherwise single line of code.

Private Sub TextBox1_AfterUpdate()

Dim strText1 As String

strText1 = Me.TextBox1.Text

'Replace ASCII 13 character with a null (or nothing).
strText1 = WorksheetFunction _
.Substitute(strText1, Chr(13), "")

Sheets("Sheet1").Range("B1") = strText1

End Sub

Will await your reply.

 

[sam]

Hey OssieMac, Thanks for all your help
I have excel 2007. I got it to work with this line of code:

Range("A1").Value = Application.Substitute(TextBox1, Chr(13), "")