How do I set a cell to auto expand when it has a formula?

[Guest]

I have a formula in a cell (destination) that derives a text field from
another Excel Sheet (Source). When I enter new text in the Source sheet, as
expected it populates in teh destination dield. However, the destination
field needs to be adjusted to display the entire contents each time the the
text lenght increases. Setting "Wrap Text" option does not resize the field
each time the length increases. I need this feature as part of my work, and
currently I do this manually. Thanks your suggestion

 

[Guest]

How about pasting this into the sheet object in the VB Editor. Regardless of
what is placed into the cell, the column will resize.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireColumn.AutoFit
Application.EnableEvents = True
End Sub

 

[Guest]

Thanks for responding. However, that did not resize the cell automatically.
SriGog

 

[Guest]

Also, I forgot to add that the cell where I need to display the data is a
merged cell. Is that adding to the issue?

 

[Gord Dibben]

The merged cell is not adding to the issue......it is the cause of the issue.

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA code to do that.

Here is code from Greg Wilson.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub


Gord Dibben MS Excel MVP

Also, I forgot to add that the cell where I need to display the data is a
merged cell. Is that adding to the issue?

Gord Dibben MS Excel MVP

 

[Guest]

Debbie:
Thank you. I was able put this code in the Worksheet_selectionChange and can
manage the auto sizing. However,this requires me to click on the cells that I
want to auto-size (which is the trigger for the resize). I looked to see if
there is any way to make it a worksheet level macro (That is select the
worksheet and it resizes all the fields that need to ne resized). I could not
find a way. I wish there is a mechanism for that. If you know, I will be
grateful to you. This is needed on a work book with 60 worksheets. Thank you.