Auto Re-sizing Textboxes

[Glaswegian]

Hello

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
rngCell.Select
rngCell.EntireRow.Insert
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.

Regards

[Killian]

Hi there,
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

[Glaswegian]

Killian

Thank you!!

It works well with XL97.

Appreciate your help.

Regards