Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/b] right before the first IF statement and
get this
"compile error: Wrong number of arguments or invalid property assignment"
the *.select* in the added code is highlighted.
Thanks everyone for your time, I appreciate it.
"malik641" wrote:
Okay, this works.
Try this:
Make sure you keep the
PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SUB
That Jim and Steve told you to change.
and in your *Sub AutoFitMergedCellRowHeight () * macro right above the
first if statement add:
ACTIVECELL.OFFSET(-1, 0).SELECT[/b]
AND HERE IS WHAT YOU END UP WITH.
SUB AUTOFITMERGEDCELLROWHEIGHT()
DIM CURRENTROWHEIGHT AS SINGLE, MERGEDCELLRGWIDTH AS SINGLE
DIM CURRCELL AS RANGE
DIM ACTIVECELLWIDTH AS SINGLE, POSSNEWROWHEIGHT AS SINGLE
[b]ACTIVECELL.OFFSET(-1, 0).SELECT
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
PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SUB
---------------------------------------------------
Now after you enter the text and you press enter it will change it for
you automatically and remain to be the cell that you entered the text
in.
Hope this is what you were looking for!
--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388557