Excel Text Box Resizing RRS feed

  • Question

  • I have a spread sheet with a text box inserted mid sheet. The text box is formatted to fit text so it wraps text and enlarges to accommodate the required space.

    However, I need the cells below the text box to proportionately move down when the text box enlarges instead of overlapping the occupied cells below.

    Wednesday, February 20, 2019 12:52 PM

All replies

  • Hi Mark NT,

    I'd like to confirm:
    Do you have a text box which size will be changed (its width and height will be changed) automatically according to volume of text in the text box?  (usually it is hard to realize)

    If you can do this with VBA code, you could change size of cells (size of Row or Column?) according to size of the text box.


    Ashidacchi --

    Wednesday, February 20, 2019 11:53 PM
  • Hi Mark NT,

    It is easy to change size of a TextBox when size of a Cell is changed.

    Please explain your requirement more detail, or make it clear.


    Ashidacchi --

    • Edited by Ashidacchi Thursday, February 21, 2019 3:26 AM
    Thursday, February 21, 2019 3:22 AM
  • I tried to paste a section of my spreadsheet but this site would not let me, soo... I'll try to explain further:

    I have a text box in the middle of my spreadsheet that is the width of the page and as inserted is about two rows high.

    Its formatted to wrap and fit text, so if I have a lot of text it will expand down to accommodate the needed space, however it expands over and hides the cells below.

    So can I get all the cells below the text box to move down if my text box enlarges in height to accommodate the text inserted?

    Thursday, February 21, 2019 3:23 PM
  • Hi Mark NT,

    I'd like to confirm:
      (1) which "TextBox" do you mean?  A shape on worksheet, ActiveX TextBox, or Form Control? 
      (2) do you want to change Row Height or Column Width by code, according to size of a TextBox?

    Could you insert an image of your worksheet into your post, or share it via cloud storage such as OneDrive, Dropbox, etc?


    Ashidacchi --

    Friday, February 22, 2019 2:15 AM
  • Hi Mark NT,

    I'm not sure I can understand your requirements correctly.
    But I have many to do, so, I've made a sample.
    Can this satisfy your requirements?

    Private shpHeight, shpWidth As Single
    ' --- Worksheet: Activate
    Private Sub Worksheet_Activate()
        ' ---
        With Shapes.Range(Array("ShapeTextBox"))
            Selection.ShapeRange.TextFrame2.AutoSize = msoAutoSizeNone  ' -- サイズ変更しない
            Selection.ShapeRange.TextFrame2.WordWrap = msoTrue  ' -- 図形内でテキストを折り返す
            Selection.Placement = xlMove    ' -- セルに合わせ移動するがサイズ変更はしない
            ' --- サイズを変数に格納
            shpHeight = .Height
            shpWidth = .Width
            ' --- 初期サイズを表示
            Range("A2").Value = "W=" & Format(.Width, "0.00") & vbLf _
                              & "H=" & Format(.Height, "0.00")
        End With
    End Sub
    ' --- Worksheet: SelectionChange
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ' ---[B3]セル: サイズ変更
        With Shapes.Range(Array("ShapeTextBox"))
            If (.Height <> shpHeight) Or (.Width <> shpWidth) Then
                Rows(2).RowHeight = .Height + 10
                Rows(3).RowHeight = .Height
                Columns(2).ColumnWidth = .Width / 6.4   ' --<< change according to your environment
                ' ---
                shpHeight = .Height
                shpWidth = .Width
            End If
            ' --- 列とシェイプの単位をチェック
            Range("B3").Value = _
                    "TextBox W=" & .Width & vbLf _
                  & "Cell    W=" & Columns(3).ColumnWidth
            ' ---TextBoxシェイプ: 位置変更
            .Left = Range("B2").Left + 5
            .Top = Range("B2").Top + 5
        End With
    End Sub

    Ashidacchi --

    Friday, February 22, 2019 3:46 AM
  • Hi Mark NT,

    Try putting this code to the TextBox_change event (if it is an ActiveX control):

    Dim sht As Worksheet Dim intHeight As Integer Static intInformed As Integer intHeight = 15 'Setting the original or starting cell height

    'Replace "TextBox1" with the actual name of your textbox

    Set sht = TextBox1.Parent With TextBox1 If .Height <= 409 Then 'maximum height of the row on the sheet is 409, so we should not exceed it 'Cells(1, 1) below - is the cell where your textbox is put over If .Height > intHeight Then sht.Cells(1, 1).RowHeight = .Height 'increasing the rowheight according to the height of textbox ElseIf .Height <= intHeight Then sht.Cells(1, 1).RowHeight = intHeight 'setting rowheight to the starting cell height End If Else If intInformed = 0 Then MsgBox "Reached maximum row height!" & Chr(10) & _ "Row height will not change anymore!" intInformed = 1 End If End If End With

    • Edited by Vitalii P Friday, February 22, 2019 1:39 PM
    Friday, February 22, 2019 1:36 PM