Excel Input Form Question

Posted on 2009-02-17
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
    LVL 13

    Expert Comment

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

    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

    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

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

    Author Closing Comment

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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Understanding and Setting Up a SMTP relay service 17 54
    Excel Formula 6 51
    Office 2010 errors on install 8 15
    Vlookup for IP 3 36
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now