ExcelBanter - Wrap Text doesn't work in Merged Cell

Wrap Text works in a merged cell but Autofit does not.

You need VBA event code to make it work.

Here is code from Greg Wilson.

Make sure Wrap Text is enabled for all cells in question.

I also threw in some sheet unprotect and protect lines

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
Me.Unprotect Password:="justme"
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
Me.Protect Password:="justme"
End Sub

Gord Dibben MS Excel MVP

On Wed, 29 Jul 2009 12:26:02 -0700, Steve
wrote:

Excel 2003 I have a form set up that limits access to only unlocked cells
when protection is on. Some of the cells are merged in a row, and are
intended to hold text (sentences) that might vary in length, so I checked the
Wrap Text box. I also set the Row Height to AutoFit. However the row height
does not expand to show a long text entry, either when the spreadsheet is
protected, or even when unprotected and I double-click the lower cell
boundary in the row header.

Am I missing something or isn't this really a capability? Would I have to
set up a macro to do this, either automatically on exit from the cell or by
the click of a button?