Activex Textbox Autosize Bug

Question

According to Excel 2010 help setting the autosize property of an activeX textbox is NOT supposed to change the currently defined width of the textbox.But if the text in the textbox is less than 1 line in length (say 4 words), the activex textbox shrinks to the width of the 4 words when the autosize property is set to True. Is this a bug and is there any work around?

Answer 1

Hi,

This is not my undertanding of the description given in help.

Try to add a Change event to correct it

Private Sub TextBox1_Change()

TextBox1.Width = 125

End Sub

Regards

JY

Answer 2

I want to be able to print the page as a snapshot report of an account's status. A scrolling textbox does not accommodate that.

Answer 3

Hi,

Just to add, Is it really necessary to expand the Vertical Border size ?  The inner content will expand/scroll as needed.

Regards

JY

Answer 4

FYI: Quote from 2010 VBA help for Autosize Property:

"For a multiline text box that contains text, setting AutoSize to True automatically enlarges the TextBox vertically to display the entire text. The width of the TextBox does not change."

It doesn't say "For a multiline textbox that contains more than one line of text, "

Nor does it say "For a textbox that contains multiple lines of text"

I will try your solution though. Thanks.

Update:

Tried your solution. It does work. Thanks again. But you also have to do the same thing in the Textbox1 LostFocus event. If there is less than a full line of text in an activex MultiLine Textbox placed on on a worksheet, the textbox will shrink its width to the width of the words on the first line with any change to the textbox or any exit from the textbox - despite the fact that AutoSize is set to true. That clearly doesn't comport with the help file.

I've been away from VBA for awhile and I know activex textboxes have been around for some time, but to be blunt about it, these controls really don't seem ready for prime time. I've run into an awful lot of problems with these and half the code I'm writing is just to get around the fact that they don't work as they are supposed to work according to the documentation.