Text Box Problem

[ride2995]

I need the help of some more experienced Excel users so please feel
free to chime in!
I use office 2004 for Mac. I inserted a text box into an Excel
document. Despite setting the text box size and the margins, (most
importantly the bottom margin), the text box allows the text that I
type in to extend beyond the bottom margin. Actually, the text scrolls
up allowing me to type many more lines of text than I want. Since much
of the text ends up outside of the bottom margin, it does not show up
in a print preview of the document. This concerns me because I am
designing a document that others will need to use. I don't want them
to type in their text only to find that much of won't end up on the
page. The text box in this document must have a defined amount of
space. I even played around with text boxes in Word. In Word, the
text box "grew" to allow me to type as much text as I wanted. So, the
question is, in Excel, how can I constrain the size of the text box so
that when others type into it, it will not scroll up allowing them to
continue typing beyond the boundaries of what will be visible on the
page?
Thanking you in advance for your help...

 

[Jim Gordon MVP]

Hi,

It might be better to use a cell as an input box because you can use the
Data menu for conditional formatting and limit the number of text
characters that can be entered.

-Jim

 

[ride2995]

Hi Jim,

Thanks for your prompt reply. I've told the people that I'm working
for that the cell is best but they are really having trouble adjusting
to inputting text that way. They much prefer the text box. Any other
thoughts?

 

[Bob Greenblatt]

Hi Jim,

Thanks for your prompt reply. I've told the people that I'm working
for that the cell is best but they are really having trouble adjusting
to inputting text that way. They much prefer the text box. Any other
thoughts?

You can right click the text box, select alignment and check Resize to fit
text and wrap text. The text box will then grow vertically as text is
entered. Alternately, you might want to assign a macro to the text box and
trap the entry to enforce a size or text length.

 

[ride2995]

Can someone tell me how to assign a macro to the text box and trap the
entry to enforce a size or text length?

 

[JE McGimpsey]

Can someone tell me how to assign a macro to the text box and trap the
entry to enforce a size or text length?

Plain Text Boxes can have macros assigned by CTRL-clicking them and
choosing "Assign Macro...". However, they can't trap the input. That can
be done with Controls Toolbox Textboxes, which are ActiveX controls and
don't run in MacXL.

OTOH, if you embedded a TextBox in a UserForm, the TextBox has several
events that you can use to enforce a size or text length.

 

[Bob Greenblatt]

Plain Text Boxes can have macros assigned by CTRL-clicking them and
choosing "Assign Macro...". However, they can't trap the input. That can
be done with Controls Toolbox Textboxes, which are ActiveX controls and
don't run in MacXL.

OTOH, if you embedded a TextBox in a UserForm, the TextBox has several
events that you can use to enforce a size or text length.

Well, it's pretty messy, but I guess one could accomplish what ride2995
wants to do by using a user form with a text entry box. Use a macro to trap
whatever events are necessary to verify contents and/or text length. Assign
a macro to the on sheet text box that transfers the text to the form's text
box, shows the form, and then transfers the text back to the worksheet text
box. I'll work, but it is not pretty.