Auto-resize cell dimensions based on Text Box size

Question

Auto-resize cell dimensions based on Text Box size

Arthur Primee 0

Hi everyone,

Is there a way to link a Text Box to a cell in Excel so that the cell (or row/column) automatically expands or shrinks when the Text Box size changes?

Alternatively, can a standard Excel cell automatically adjust its height and width dynamically as more text is typed into it, similar to how a text box behaves?

0 comments

2 answers

Your answer

Answer 1

A standard Excel cell does not behave like a text box that grows in both dimensions as text is typed. What is supported is:

Row height can be adjusted automatically for wrapped cell content.
Column width can be changed manually, and wrapped text will reflow to fit that width.
Text can be linked to a shape or text box, so the displayed text updates when the cell changes.

For a normal cell:

Select the cell or range.
On Home, select Wrap Text.
If all text is not visible, on Home > Format > Auto-fit Row Height.

This makes the row height adjust to show wrapped text. When the column width changes, the wrapped text adjusts automatically, but the context only supports using Auto-fit Row Height to show all wrapped text.

If the goal is to show cell content inside a floating object, a text box or shape can be linked to a cell:

Insert a Text Box or shape.
Select it.
In the formula bar, type =.
Select the source cell, or type a reference such as =Sheet1!F2.
Press Enter.

That creates a live display of the cell’s contents in the text box or shape. The supported content does not describe automatic resizing of the worksheet cell, row, or column based on the text box’s size.

If an ActiveX TextBox is being used, these properties control its behavior:

MultiLine: allows multiple lines
WordWrap: wraps long lines
AutoSize: adjusts the TextBox size to display text
LinkedCell: links the control value to a worksheet cell

That means the TextBox itself can be configured to resize or wrap, and it can store or display its value through a linked cell, but the available information does not support automatic worksheet row/column resizing in response to TextBox size changes.

Answer 2

Dear Arthur Primee,

Based on the description you provided, you would like to link a Text Box to a cell in Excel so that the cell or row/column automatically expands or shrinks when the Text Box size changes. In that case, please follow the steps below to run the VBA macro.

This is a custom workaround rather than an official Excel feature. It works on Excel desktop only (not Excel for the web), and the file must be saved as a macro-enabled workbook.

1.Select Insert > Text Box, then draw it over the cell you want to resize (for example, cell B6).

2.Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

3.Add the macro

Press Alt + F11 to open the VBA Editor.
Select Insert > Module.
If the new module already contains any text, press Ctrl + A and Delete to clear it.
Paste the following code:

Sub FitCellToTextBox()
    Dim shp As Shape
    Dim cel As Range
    Dim i As Integer
    Set shp = ActiveSheet.Shapes(Application.Caller)
    shp.Placement = xlFreeFloating
    Set cel = shp.TopLeftCell
    If shp.Height > 409.5 Then
        cel.EntireRow.RowHeight = 409.5
    Else
        cel.EntireRow.RowHeight = shp.Height
    End If
    For i = 1 To 3
        If cel.Width > 0 Then
            cel.EntireColumn.ColumnWidth = cel.EntireColumn.ColumnWidth * shp.Width / cel.Width
        End If
    Next i
    shp.Top = cel.Top
    shp.Left = cel.Left
End Sub

4.Press Alt + Q to return to Excel, then save the file.

5.Assign the macro to the text box

Right-click the border of the text box and select Assign Macro.
Select FitCellToTextBox, then select OK.

Here is the result in my test environment after running the macro, where the cell resizes to match the text box:

Untitled

Regarding your second question, "Alternatively, can a standard Excel cell automatically adjust its height and width dynamically as more text is typed into it, similar to how a text box behaves?", please refer to the answer provided by the AI assistant below.

Hope this helps. Thank you for your patience and your understanding. I'm looking forward for your reply.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.