Autofit row height when retrieving text via formula

[andy62]

After reading through all the row height responses (most of which address
mergred cells - not my issue), I don't see where this has been addressed. I
have a formula that can retrieve a highly variable amount of text. Since
that cell is never directly edited, it does not trigger autofit to resize the
row height. If I double-click on the lower border of the row, or invoke
Autofit manually, it does resize the row. But I need that to happen
automatically (so I don't get calls from users telling me that text is
missing). This could make the solution easier: all the changes that would
increase/decrease the amount of text in the cell happen on another worksheet,
so maybe all I need is an event macro that invokes Autofit when returning to
the target sheet.

I am using xl2003, and the target sheet is password protected.

TIA

 

[Dave Peterson]

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

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

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

 

[andy62]

Thanks, Dave. That works great. At first, with the target worksheet
protected, I was getting a runtime error, but I realized I needed to "allow
row formatting" under the protection settings.

 

[Dave Peterson]

You could have added a line to unprotect the sheet, resize the row heights, and
reprotect the worksheet--useful if you were using xl2k and earlier.