Wrapping Text in Merged Cells - Excel 2002

[Cornelia]

I have merged cells which have condiational formatting applies, the wrap text
is checked and the text will not wrap.

I have tried a few of the macros which were mentioned in other questions
with no success.

Anyone have any suggestions.

 

[Gord Dibben]

Wrap text text works fine in merged cells.

However.............autofitting of row height does not work.

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micr...60cbeb27874?lnk=st&q=&rnum=4#ca1c160cbeb27874

If this code is one of the macros you tried, please post back and explain
what is or is not happening.


Gord Dibben MS Excel MVP

 

[Cornelia]

Hi Gord,

I cut and pasted the macro below and I am getting a error on the ----
ma.MergeCells = False ---- line, any suggestions?


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
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
End Sub

 

[Gord Dibben]

I would guess the sheet is protected.

Try this version.

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
On Error GoTo endit
ActiveSheet.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
endit:
ActiveSheet.Protect Password:="justme"
End Sub


Gord