View Full Version : Solved: Auto Re-sizing Textboxes

03-16-2005, 04:17 AM

I?m having a problem with Textboxes.

One of the sheets in the workbook I?m creating will have around a dozen textboxes. Users will input to some or all of these boxes. Once input is complete, the sheet will be printed ? therefore all text in the textboxes must be visible.

My problem is this ? how can I automatically resize the textboxes to ensure that

all text is visible
textboxes do not overlap, i.e. each textbox will retain it?s position relative to it?s neighbours
I tried something like this code, but it is only a partial (and not very good) solution

Private Sub CommandButton1_Click()

Dim tbHeight As Integer

Dim txBox As OLEObject

Dim rngCell As Range

Set txBox = Sheets("Sheet1").OLEObjects("TextBox1")

Set rngCell = txBox.BottomRightCell

tbHeight = txBox.Height

txBox.Height = txBox.Height + 10



End Sub

As you can see I?ve resorted to using a button to run the code. I?ve tried using LineCount but I keep getting a message that I need to set the Focus first. I then try and set the focus but then get another message that ?Object does not support this property or method?. I also tried the Change Event for the textbox but, again, had problems with the focus. (this could be XL 97 related of course)

Textboxes are from the Control Toolbox. I had a play with Forms textboxes and using Automatic Sizing, but had problems with overlapping.

I have no preference for either type of textbox.

This is probably something I should be able to work out, but I think a fresh eye is needed.

Thanks for any assistance.


03-16-2005, 05:52 PM
Hi there, :hi:
If you use the textbox's 'Change' event, you can update the position as you type and won't need the buttons. I don't have 97 to test this but it works in 2002
Private Sub TextBox1_Change()

TextBox1.AutoSize = True
TextBox2.Top = TextBox1.Top + TextBox1.Height + 10

End Sub
It would probably be better to put the AutoSize=True for each textbox in the WorkBook_Open event - it only needs to be set once...
I've noticed with textboxes that the damn text always scrolls up before the resize happens so you can't see the first line. I think maybe this can be fixed though - I bodged it by adding this to the code above If Left(TextBox1.Text, 1) <> Chr(13) Then TextBox1.Text = Chr(13) & TextBox1.Text

03-17-2005, 02:10 AM

Thank you!!

It works well with XL97.

Appreciate your help.