Automatically expand text box

I'm not sure I understand what you want. You say that if it is set for auto-sizing it expands to the right, but you want it to expand downward. Yet you say that you don't want to hide the data below.

When you expand a text box, either manually or when it's done automatically, it covers cells that are next to it. So if I have data in cells below a text box and I expand the text box downwards, the cells with data will end up being behind the text box and I won't see them anymore.

The only way I have found to expand the size of a text box and not hide anything (basically pushing down the entire worksheet) is to expand the row height (or insert a row) which is covered by the text box with the option 'Move and size with cell' on in the text box properties.

This is the case either with regular text boxes or with 'form' text boxes (inserted through the control toolbox).

What I want is for my text box to expand downwards without hiding any data that is in cells below - in effect expand downwards by pushing down the rest of the worksheet. Because I'm anticipating this document to be used by all kinds of people, I need this feature to be really simple: either the text box automatically expands downwards AND pushes the rest of the worksheet down at the same time; or I thought maybe a macro that would increase the height of a row that's behind the text box - a button-type feature where you 'Click here to expand text box' if you want to make the text box bigger (which would need to work repeatedly) (those are the thoughts of a non-VB expert, so bear with me!)

hopes this makes more sense

thanks for your help!!

It's confusing to use the term "form text box" to refer to a text box added using the Control Toolbox. Within the VBE you can add a User Form, which is a pop-up that can include various controls (inlcuding a text box). You would typically use VBA code to control the user form (making it appear, disappear and transferring the data entered to the worksheet, if desired).

So, perhaps, a User Form is really what you need? It would be fairly easy to make it do the following:
1. A window (the user form) pops up, prompting the user to enter some text.
2. After entering the text, the user clicks on a button named "Submit" on the user form, below the text box.
3. The information entered in the text box is transferred to the spreadsheet (entered into some cell).
4. The user form goes away.

Here is a step-by-step tutorial on creating a simple userform:
http://www.contextures.com/xlUserForm01.html