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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,362
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 
Last edited:
post#10 option1 code
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
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
post#10 option2 code
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
 
Last edited:
Upvote 0
and finally option3
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :confused:

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
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


Edit the textbox and let me know...
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :eeek:
- aplologies I should have spotted that earlier :oops:


Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top