Problem: Merging Cells and Automatic Rowheight adjustment

[J. Moleman]

Once more, I'm really stuck:

Can anyone help me with the following problem:

(Firstly I use a Dutch version of Excel97 so I may not have translated the
names of the functions correctly.)

If you type text into a cell that does not fit, the characters that don't
fit
aren't shown. But when you choose the appropriate option in the properties
of
the cell, Excel97 will insert a "soft-return" in the cell. When you exit the
cell (by [enter]) Excel97 will automatically adjust the rowheight so all of
the I however chose to merge cells and now Excel97 no longer adjusts the
rowheight of the merged cells automatically. The insertion of the
"soft-return" still works but you have to adjust the rowheight manually.
text
is visible.

Of course Microsoft told me this was "by-design" and Excel "didn't offer
this
functionality". That didn't solve my problem.

Anyone who has a macro that runs in the background or some other trick?

Thanks for reading, Jasper

[Ogilvy, Thomas, W., Mr., ODCSLOG]

The only time you would need this to occur when you enter a value in the
cell. So you could use the Worksheet_change event (in excel 97).

Target.entirerow.autofit
should cause the row to be adjusted. This assumes you already have the
word wrapped option set.

Regards,
Tom Ogilvy

[J. Moleman]

Thanks for your reaction,

The AutoFit sounds like what I was looking for, so I tried. Unfortunatly it
doesn't work for me.
I entered the following code:

Private Sub Worksheet_Change(ByVal Worksheet As Range)
Worksheet.EntireRow.AutoFit
End Sub

It starts as it's supposed to, but the row remains the same height no matter
the number of characters. If I replace "AutoFit" with "Select", the entire
row is selected after every entry of data, so that does work.

Again this is all done with merged cells. If I try this with a single cell,
it works beautifully. ( As it does without any code)

Happy Easter, Jasper

Ogilvy, Thomas, W., Mr., ODCSLOG heeft geschreven in bericht
<278EF0D03897D111880...@dadc020.hqda.pentagon.mil>...

[Ogilvy, Thomas, W., Mr., ODCSLOG]

J.
My mistake. It doesn't appear to work manually, so I guess it won't
work in code either. Here is a workaround. It uses as scratch sheet to
determine the height of the row (you can hide this sheet). May seem
kludgey, but it works until you get a better solution:

Put this function in a general module.
Function FindMergeHeight(Target As Excel.Range) As Single
Set rng = Target(1, 1)
sfont = rng.Font.Name
For i = 1 To Target.Columns.Count
sngWidth = sngWidth + Target.Columns(i).ColumnWidth
Next i
With Worksheets("Scratch").Cells(5, 5)
.ColumnWidth = sngWidth
.Font.Name = sfont
.Font.Size = rng.Font.Size
.Value = rng.Value
.WrapText = True
.EntireRow.AutoFit
FindMergeHeight = .EntireRow.Height
Debug.Print FindMergeHeight, .EntireRow.Height
End With
End Function

Here is the event code for the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.MergeCells Then
sngHeight = FindMergeHeight(Target)
Debug.Print sngHeight
If Target.Rows.Count > 1 Then
For Each rw In Target.Rows
rw.EntireRow.RowHeight = sngHeight / Target.Rows.Count
Next
Else
Target.EntireRow.RowHeight = sngHeight
End If
End If
End Sub

HTH,
Tom Ogilvy

-----Original Message-----
From: J. Moleman [mailto:j.mo...@heracles.net]
Posted At: Friday, April 02, 1999 9:01 AM
Posted To: programming
Conversation: Problem: Merging Cells and Automatic Rowheight adjustment

[Jim Rech]

I had a feeling you'd take the challenge, Tom<g>. Here's my effort too for
the record. The key differences from yours:

1. I do it in place rather than on a scratch sheet.
2. If the row height is already greater than I need I don't shrink it. How
do you know another merged cell on the row doesn't need the current height?
If you want to leave it to the user to decide then your way is better.
3. I insist on only one row in the merged cell. Sort of the same philosophy
as 2. You could screw up another row's height

Jim

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

[Ogilvy, Thomas, W., Mr., ODCSLOG]

Jim,
My solution was definitely current cell centric with respect to the row
height. I like your idea of doing it inplace. I thought of that
briefly, but was already going the scratch sheet approach and wasn't
looking at the mergecells property as a toggle - definitely the correct
approach.

Regards,
Tom Ogilvy

-----Original Message-----
From: Jim Rech [mailto:jar...@kpmg.com]
Posted At: Friday, April 02, 1999 1:40 PM
Posted To: programming
Conversation: Problem: Merging Cells and Automatic Rowheight adjustment
Subject: Re: Problem: Merging Cells and Automatic Rowheight adjustment

[J. Moleman]

Thanks Jim, works exactly as I wanted it to.
Sorry T, I never tried your second attempt.

I removed the IIf statement so the rowheight is adjusted down too. (There
are no other cells in the row)

Jasper