Excel Input Form Question

Posted on 2009-02-17
Medium Priority
Last Modified: 2012-08-14
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?
Question by:kilcoyne49
  • 3
  • 2
LVL 13

Expert Comment

by:Brian Withun
ID: 23667014
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

LVL 13

Accepted Solution

Brian Withun earned 750 total points
ID: 23667056
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

Author Comment

ID: 23672566
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.

Author Comment

ID: 23672595
I had to send the file again as I forgot to unprotect it. Here you go.

Author Closing Comment

ID: 31547985
Thanks for your help. I did use this solution but will continue to look for a method as well. Thanks again.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question