Wrap text in merged cells

[Guest]

I have a file where I have merged cells a10:d:10 and formatted it to wrap text.
My problem is that the row-height not changes automatically, which always
happends if I wrap text in cells that are not merged.
I there any sulotion for this?

 

[JE McGimpsey]

The best solution is not to use merged cells - they're far more trouble
than they're worth. If you're merging in order to center a title, do
this instead: put the title in A1. Select A11. Choose
Format/Cells/Alignment and select "Center Across Selection" from the
Horizontal dropdown.

If you need to keep the cells merged, then you'll need to use something
like this macro by Jim Rech:

http://groups.google.com/group/microsoft.public.excel.programming/browse_
frm/thread/a5cbe0ee8e6c2a10/93c6bca447bd8902#93c6bca447bd8902

(or http://elfurl.com/2gguv, if the above linewrapped link doesn't work)

 

[Guest]

Is there any way to get this macro to run automatically while pressing Enter
after typing a long sentence?
I get the macro to work, but only if I create a Button to run it from.

"JE McGimpsey" skrev:

 

[Gord Dibben]

Lena

Here is event code from Greg Wilson that runs when you ENTER out of a merged
cell.

Note: wrap text format must be preset on the merged cells.

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

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks!
Now I understand.

"Gord Dibben" skrev:

Lena

Here is event code from Greg Wilson that runs when you ENTER out of a merged
cell.

Note: wrap text format must be preset on the merged cells.

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

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP

 

[Guest]

No happines lasts forever! When I started to work in my file, someting new
showed up.
After entering a text, which wraps perfectly I need to Delete the content in
the cell, and presses Delete. Excel gives me the message that the cell I try
to change is locked.
For some reason, that I can not understand, the merged cell that from the
start is formatted "not locked" now is formated like the cell both is locked
and not (the square is grey). This also happends when I unmerge and then
merge manually.

It is not logical since it works fore some merged cells but not for all in
the same worksheet.
If anyone understands what I am trying to explain, please help!

"Lena_Office" skrev:

 

[Guest]

I don't replicate this but you might try the following. If you are protecting
it and using a password then, In place of the word "password" below you would
hard code your password. Also remove the leading apostrophe. If you are not
using a password then delete this part of the code (or ignore).

Minimal testing:-

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
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' "password"
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
On Error Resume Next
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
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' "password"
End If
End With
End Sub

Regards,
Greg