Is Auto Expansion (i.e., wrap text) of a cell possible when thecell's contents are based on another

[Dave K]

I am trying to display the results from one tab (Tab 1) in another
(Tab 2).

So, for example, tab 2 contains the formula "=Tab1!A2".

However, when the results are too large to fit the cell in tab 2
(i.e., the cell that contains a formula that draws from a cell in tab
1), the wrap text feature does not work unless i first double click in
the cell in tab 2.

Is there any way around this? Can the wrap text feature work
automatically somehow? Or will i need to double click in every cell
that contains text that doesn't fit into the cell.

Thanks for any suggestions, or VBA code, that might make this
possible.

 

[Dave Peterson]

It's not the wrap text feature that's failing, it's really the rowheight that's
not adjusting, right?

If that's true, then this is just how excel works.

But you could add an event macro that adjusts rowheights (or columnwidths) each
time excel recalculates.

Saved from a previous post:

Excel doesn't change the size of rows/columns when a formula recalculates. I
would imagine that there would be people just as upset if their meticulously
designed forms changed with every recalculation.

But you could use the worksheet_calculate event to resize rows or columns.


Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

This code would be located in the worksheet's module that needs this behavior.

Rightclick on the worksheet tab, select view code, and paste this into the newly
opened code window (usually on the right).

Then back to excel and recalculate.

 

[Dave K]

It's not the wrap text feature that's failing, it's really the rowheight that's
not adjusting, right?

If that's true, then this is just how excel works.

But you could add an event macro that adjusts rowheights (or columnwidths) each
time excel recalculates.

Saved from a previous post:

Excel doesn't change the size of rows/columns when a formula recalculates..  I
would imagine that there would be people just as upset if their meticulously
designed forms changed with every recalculation.

But you could use the worksheet_calculate event to resize rows or columns..

Option Explicit
Private Sub Worksheet_Calculate()
     application.enableevents = false
     Me.Rows.AutoFit
     'or be specific
     Me.Rows("1:33").AutoFit
     application.enableevents = true
End Sub

This code would be located in the worksheet's module that needs this behavior.

Rightclick on the worksheet tab, select view code, and paste this into the newly
opened code window (usually on the right).

Then back to excel and recalculate.

thanks very much!