Next Line in ActiveX Text Box

[fred]

hi all,


I'm using an activeX text box in excel. I can get the box to "warp" the text. However, when I hit the "Enter" key, excel won't move to the next line. Seems to me that the text box can only display as a continuous sentance but not "paragraph"? I tried looking into the text box property and tweak around. But I can't find a way to display the next sentance on a different line.


Please kindly advise if it is even possible? Thank you very much.

 

[fred]

it should be "wrap" not "warp".

 

[Hui]

Fred

You need to do 2 things

1. Select your text box and change the Multiline Property to True

2. In the code you need to add a Carriage Return or Line Feed to the text at the right spot. eg:

TextBox1.Text = "Line 1" + vbLf + "Line2"

or

TextBox1.Text = "Line 1" + vbNewLine + "Line2"

 

[fred]

Hi Hui,


#1 was easy. I had that figured out. But I have problem understanding #2 where I have to add a Carriage Return or line Fee to the text, in the "code".


Question:

- Do you mean the file has to be saved in .xlsm first?

- "code" do you mean under "View Code" under property? I'm using MS2007.

- When i did your suggestion the text box shows "Line 1" on first line and "Line2" on the next. It looks correct but do you mean users have to go into the code and enter text there?

- I opened up Visual Basic under "view code" and I only see


Private Sub TextBox1_Change()


End Sub


- I can't find "code" under "Alphabetic" or "Categorized".

 

[Hui]

Hi Fred

I maybe wrongly assumed that because you were using an ActiveX TextBox that you were using VBA or using it as part of a form.


If your using an ActiveX TextBox is on a worksheet with a cell link the process is similar

1. Select your text box and change the Multiline Property to True

2. Set your LinkedCell to a value eg: B2

3. In B2 your text will need to have a LineFeed character added to it eg:

=CONCATENATE("Line 1",CHAR(10),"Line 2")

or

="Line 1" & CHAR(10) & "Line 2"

 

[fred]

Hi Hui,


I see what the linked B2 and the #3 does now. However, that would mean that the text has to be typed up in cell B such that, as an example, the text box would displace two lines where B2 ="Jan 10, 2011 customer called to dispute invoice.'&CHAR(10)&"Jan 14, 2011 customer service rep Jane Doe (ID:1234) resolved invoice dispute. Case closed."


I was just thinking to provide the text box to the users that they can type directly into the text box. And if the writer ends a paragraph by hitting the enter key. the text will continue on the next line. At the moment, it is one continuous paragraph.


May be it is a limitation on activex text box. it looks to me that it can't displace two lines without going into cell B2, which I guess users may not know how to do that.

 

[Luke M]

If you're wanting to just type directly into a box, I think the ActiveX text box is the wrong way to go. Can you use the Text box from the drawing menu, or perhaps the one from the forms menu? Both of those appear to let you press the Enter key to start a new paragraph, if desired.

 

[fred]

Hi Luke,


The text box under form menu is grey out. What do I need to do to fix that? Thx!


Fred

 

[Luke M]

Hmm. Make sure you are not currently editing the ActiveX box? I'm using 2003, so things are a little different...can you use the text box from the drawing menu/bar?

 

[fred]

Yeah. I can use the text box. I guess it's ok the activex text box is only one paragraph (if it is in fact the case). the text box is meant to be memo only, not a place for full report.


I just think that a text box under form control looks better. And unlike excel, this text box which I'm typing allow me to have 2 or more paragraph.


Any way, thank you very much for the suggestion! I really appreciate it.

 

[fred]

hi all,


I found the answer from Micro direct.


"To enter a new line, the user must press either CTRL+ENTER or SHIFT+ENTER, which generates a special character that is stored in the linked cell."


I'll put a note to remind the writers. Thanks all!