I have a text box created in Excel 2010 with Drawing Tools/Insert Shape/Text Box at the bottom of a form to provide a place for user input. Regardless of what combinations of settings I use, the last line of text in the text box will not print. It doesn't matter if the text box is set to a fixed size, as it is in the attached workbook, or if Autofit is set to "Resize shape to fit text." And although "Allow text to overflow shape" is not checked, text can still be entered in the space for the last line and beyond the bottom of the box by wrapping (which is on) or using hard returns (i.e., "Enter"). Text entered in the last line of the box and beyond disappears when the box is deactivated but it is not deleted.


The settings are:
Text Box/Autofit/Wrap text in shape
Properties/Don't move or size with cells *
Properties/Print object
Font = Arial 10 pt

* "Don't move or size with cells" keeps the width of the text box matched to the width of the cells in the form above the text box.

I've tried using many different fonts, but that had no apparent effect on the problem. (The font doesn't have to be Arial 10 pt; that's just what it happens to be right now.)

If I was the only person using this form, I'm sure I could remember to stop typing one line above the bottom of the box. However, many people will use this form, so I need to find a way to either prevent text from being entered past the point where it will print (the second line above the bottom of the box) or find a way to force the last line in the text box to print. It would be nice to prevent typing beyond the bottom of the box too, but telling users not to type past the bottom of the box should work, as it seems fairly logical, although the fact that they can type past the bottom of the box is not at all logical. However, telling users not to type anything in the last line of the text box isn't a viable solution, as users are not likely to remember not to, being that it doesn't make any sense, plus it will make me look incompetent. The fact that text entry cannot be stopped at the bottom of the box is bad enough. One would think that leaving "Allow text to overflow shape" unchecked would prevent text from overflowing the shape, but suppose that would be inconsistent with the strange logic of Microsoft's developers.

Intuition tells me that there may be a VBA solution, but I'm a beginner at VBA and I haven't found a VBA solution online. I assume the code to stop text entry at the point where text would no longer be printed would have to be dependent on the font, font size and dimensions of he text box. Limiting the number of lines wouldn't be a viable option, as the number of printed characters per line is different than the number in the text box, which can affect the number of lines printed.

I've spent at least a day's worth of time using trial and error and searching the Web for a solution but to no avail. If anyone can help me figure this out I would be eternally grateful.