Excel Input Form Question

I need to have a field in an Excel 2003 form I created where the user can type in a question or comment, the cell will self adjust, wrap text and expand to accomedate the text entered. How do I create that type of field in my Excel document?
Who is Participating?
Brian WithunConnect With a Mentor Commented:
After reading your question a third time, I think you're not talking about true forms at all, rather you are talking about a worksheet cell.

If you want the worksheet cell to grow as you enter text into it, you can do this easiest by turning on WordWrap.  This option is available in Format > Cells...  Click on the Alignment tab, and choose "Wrap text"

Now that cell will expand to fit whatever is typed into it.  It will not do this in real time, though.

If you want it to truly grow "as" text is entered, then you're trying to fit a square peg into a round hole, honestly.  You may consider creating a project Form with a textbox control on it (like I mentioned in the earlier answer)  That kind of control can expand _as_ text is entered into it using the technique I mentioned above, or by some other means that clever people in this forum can devise...

Brian Herbert Withun
Brian WithunCommented:
t sounds like you want a textbox control to expand itself dynamically as text is entered into it.

You'll probably need to create a "_KeyPress" event and respond to each individual keystroke entered while your textbox has the focus:

The trick will probably be deciding when and how much to expand the height and or width of your TextBox control.

One idea is to monitor the .Linecount of your textbox control.  If your textbox is going to be a fixed width, then you can just scale its height with the LineCount.  The more lines you type into your control, the taller your control will become.

Just make sure your textbox is set for MultiLine and you have enabled WordWrap.
It may help to set it to IntegralHeight, too.

You'll have to play around with the relationship between the height of your textbox font and the "scaling factor" of your textbox height.

Good Luck,
Brian Herbert Withun
Private Const TwipsPerLine = 15
Private Sub DebugText_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  ' This control will be 15 twips tall for each line entered
  ' into it.  This works best if your font is also 15 twips tall.
  Let Me.DebugText.Height = TwipsPerLine * Me.DebugText.LineCount
End Sub

Open in new window

kilcoyne49Author Commented:
Thanks I used the word wrap but I would like to understand how I could use the text box formatting. I never used code much before so where would I put the code and would I just copy and paste your code into the file or do I have to customize it and how would I do that.
I'm sending you a copy of my file for you to see. I want the two boxes at the bottom to enlarge as someone is typing. So the form could go from being one page to two or more depending upon how wordy they are.
kilcoyne49Author Commented:
I had to send the file again as I forgot to unprotect it. Here you go.
kilcoyne49Author Commented:
Thanks for your help. I did use this solution but will continue to look for a method as well. Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.