Controlling Placement of Linefeeds in a Text Box Object

SUMMARY

If you have created a Text Box object using a macro in Excel and you want to control where the individual lines of text will wrap in the box, concatenate the ANSI code for a linefeed to the appropriate position(s) in the text string.

MORE INFORMATION

The ANSI code for a linefeed is decimal number 10. You can use the CHAR function to place a linefeed in your text string in a macro.

Included below are two examples. The first example will create and enter a static text string in a text box on your document. The second example allows you to input the text string as the macro is running. For both examples, type the information into the corresponding cells on a macro sheet.

Example1

A1: =CREATE.OBJECT(6,Ref_1,,,Ref_2,,,"Line1"&CHAR(10)&"Line2") A2: =RETURN()

Ref_1 and Ref_2 are the destination cell locations for the text box from upper-left to lower-right, respectively.

Example2

A1: =INPUT("Enter a text string: ",2) A2: =CREATE.OBJECT(6,Ref_1,,,Ref_2,,,DEREF(A1)) A3: =RETURN()

In this example, you must run the macro and do the following when the input box comes up:

1. Enter the first line of text.

2. Hold down the ALT key and enter 010 from the numeric keypad (verify that the NUM LOCK key is on).

3. Release the ALT key.

4. Enter the second line of text.

REFERENCES

"Microsoft Excel Function Reference," version 3.00, page 40.

"Microsoft Windows User's Guide," version 3.00, page 568.