How do I have a linked cell auto-size to fit the linked data?

[CristinPDX]

I'm using Excel 2003.

Within a workbook I have a data entry worksheet and several "report"
worksheets. In some cases the data being entered is multiple lines of text.
The cell on the data entry page will automatically re-size to fit all the
lines of text. However, the linked cell on the "report" page does not auto
size and results in the information being cut. I have found that if the data
cell is already populated when the link is established, the report cell will
format to the current cell size. I need this to occur automatically when the
data changes on the data entry page, not just at initial linking.

Any thoughts?

 

[Gord Dibben]

The rows will not autofit when the linked data changes.

You must force a re-size by adding event code to the data entry sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
If Intersect(Target, Me.Range("A1:A50")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each ws In Worksheets(Array("Report1", "Report2", "Report3"))
ws.Rows.AutoFit
Next ws
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Note: cells on Report sheets must have wrap text enabled.

Gord