Text box that increases in size with contents and pushes other things in the spreadsheet down

[dpaton05]

Does anyone know how to make a text box where you can type things that will increase downwards in size to fit the contents? It needs to be at the top of the document, so when it increases in size, it will push other things down.

 

[Yongle]

post#10 option1 code

Private Sub TextBox1_Change()
    Dim hBox As Double, h3and4 As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(3).Top
    End With
    h3and4 = hBox - h5 - H6
    Me.Rows("3:4").RowHeight = h3and4 / 2
End Sub

 

[Yongle]

post#10 option2 code

Private Sub TextBox1_Change()
    Dim hBox As Double, h7and8 As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(4).Top + 10
    End With
    h7and8 = hBox - h5 - H6
    Me.Rows("7:8").RowHeight = h7and8 / 2
End Sub

 

[Yongle]

and finally option3

Private Sub TextBox1_Change()
    Dim hBox As Double, h As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(3).Top
    End With
    h = hBox - h5 - H6
    Me.Range("3:4", "7:8").RowHeight = h / 4
End Sub

 

[dpaton05]

Thanks for the images - that helps

1 Are you using rows 1-4 for anything else?
2 Are you using rows 7-9 for anything else?

Which way do you want the table to expand?
1. leave the size of rows 5 and 6 unchanged, line up bottom of the box with bottom of row 6, allow the box to grow upwards altering the height of rows 3-4 (ie always fits between rows 3 and 6)
2. leave the size of rows 5 and 6 unchanged, line up the top of the box with middle of row 4, allow the box to grow downwards, altering the heights of rows 7-8 (ie always fits between rows 4 and 8)
3. leave the size of rows 5 and 6 unchanged, allow the box to grow centred on rows 5-6, altering the height of rows 3-4 and 7-8 (ie always fits between rows 3 and 8)
4. Something else???

1. The first 2-3 rows are taken up by a rather large image in the header
2. I don't have the spreadsheet in front of me at the moment but from memory, I don't think I am using rows 7-9 for anything else other than having some space between the table and the location.

The second option is just what I want. I want the box to expand downwards and appear to push everything that is in the way, further down the page.

 

[dpaton05]

Using option 2 I get this error message when trying to insert an active x control text box https://www.screencast.com/t/RcZXpGKsQ

I can't insert a form control text field as it is greyed out so i can't click on it.

If I click insert, then text box, it will behave a little better but won't give me the option to change those settings, such as Multiline and Autosize.

 

[Yongle]

Unless you have protected the worksheet, that is unexpected
- and (just to make sure !!) I have tested it again and it works fine for me

To eliminate anything that may be in the existing workbook, try the following:

1 Create a new workbook

2. Insert Active-X textbox

3. Right-click on textbox \ Properties \ set the following parameters to TRUE
- AutoSize
- AutoTab
- EnterKeyBehaviour
- MultiLine
- WordWrap

4. Right-click on textbox \ View code \ paste this code into the window

Private Sub TextBox1_Change()
    Dim hBox As Double, h7and8 As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(4).Top + 10
    End With
    h7and8 = hBox - h5 - H6
    Me.Rows("7:8").RowHeight = h7and8 / 2
End Sub

Edit the textbox and let me know...

 

[dpaton05]

I made a new worksheet, put in an active x control text box, pasted the code in and it had some interesting results.

The size of the rows expanded but the text box didn't. It therefore tried to squeeze all the text into the available space. I can't send you the picture at the moment as I am at work today and don't have that program that allows me to upload screen shots.

 

[dpaton05]

This is really strange! I was trying to replicate that error and now all that happens when I try and add data to the text box, I get following error:

Run time error 1004

Unable to set the RowHeight property of the range class. I press debug and this line of code is highlighted:
Me.Rows("7:8").RowHeight = h7and8 / 2

 

[Yongle]

This should fix things
- I think the height of your initial textbox was small making the formula determining row height to go negative which Excel would baulk at
- aplologies I should have spotted that earlier

Private Sub TextBox1_Change()
    Dim hBox As Double, h As Double, h5 As Double, H6 As Double
    h5 = Me.Rows(5).RowHeight
    H6 = Me.Rows(6).RowHeight
    
    With Me.Shapes("TextBox1")
        hBox = .Height
        .Top = Me.Rows(4).Top + 10
    End With
    h = hBox - h5 - H6
    If h > 0 Then
        Me.Rows("7:8").RowHeight = h / 2
    Else
        Me.Rows("7:8").RowHeight = 0
    End If
End Sub

 

[dpaton05]

Thankyou!! That worked perfectly