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

  • Thread starter Thread starter CristinPDX
  • Start date Start date
C

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?
 
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
 
Back
Top