Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform textbox: hit enter to start writing in a new line
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform textbox: hit enter to start writing in a new line
Hi Sam,
Set Multiline property to True and Set EnterKeyBehaviour property to True -- Regards, OssieMac "sam" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform textbox: hit enter to start writing in a new line
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" wrote: Hi Sam, Set Multiline property to True and Set EnterKeyBehaviour property to True -- Regards, OssieMac "sam" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform textbox: hit enter to start writing in a new line
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. -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform textbox: hit enter to start writing in a new line
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), "") "OssieMac" wrote: 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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select or Enter into a TextBox on a UserForm | Excel Programming | |||
Can you Click the "Enter" key to go to the next line in a Textbox without Exiting the Textbox ? | Excel Programming | |||
Trying to start a second line ( ie use of enter key) in an output file | Excel Programming | |||
VBA Userform Textbox re-writing cells in Excel | Excel Programming | |||
Userform Textbox writing to a cell | Excel Programming |