I tested it at my end based on a simple worksheet set-up and it works fine.
Of course, all cells including the merged range need to be unlocked. It is
assumed you have done this. Granted, this doesn't explain why you can't tab
or click on the merged range unless you've set the EnableSelection property
to xlUnlockedCells.

The simplest solution, at this point, is to email me the workbook and I can
have a look at it this evening. If it is large and/or there is private
information, just make a copy and delete all unnecessary sheets (and code if
any) and email the stripped down version. Test the stripped-down version
first to ensure that it still exhibits this behavior.

Remove the "SpammersDie" from the below email address. Please mention Excel
in the subject because I have tons of spam to sift through.



Regards,
Greg

"Jamie" wrote:

Hi Greg, I miss spoke. I tested it without protecting the document.

Here is some more information that might help. The user can enter rows if
needed, so the Comments row can be row 22 and up. Currently there are 12
rows for the user, but as I said they can enter as many rows as they need.

I have removed the text you suggested from your coding. With the document
now protected, I cannot tab or click row 22 to enter text.

Any help you can provide is appreciated. Thanks!
--
Jamie


"Greg Wilson" wrote:

The appended code was adapted from a post by Jim Rech who, to my knowledge,
originated this approach. Your code also appears to have the same origin.

Ensure that the WrapText property of the merged cells is set to True. Paste
the code to the sheet code module: Hold the mouse pointer over the sheet tab
and right-click. Select View Code and then paste to the sheet code module.

My assumption is that the sheet is password protected. If not, delete the
line at the top: Const Pwd As String = "monkey" where monkey is assumed the
password. Also delete the two occurrences of Pwd following the Unprotect and
Protect statements. The code should Autofit the range automatically.

Dim OldRng As Range
Const Pwd As String = "monkey"

Private Sub Worksheet_SelectionChange(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 Protected As Boolean

Protected = False
Set c = Cells(22, 1)
If OldRng Is Nothing Then Set OldRng = c
If Not Intersect(OldRng, c) Is Nothing Then
Application.ScreenUpdating = False
If Me.ProtectContents Then
Protected = True
Me.Unprotect Pwd
End If
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
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
If Protected Then Me.Protect Pwd
Application.ScreenUpdating = True
End If
Set OldRng = Target
End Sub

Regards,
Greg

"Jamie" wrote:

I'm working in Excel 2002.

My spreadsheet consists of columns A - AH, 22 rows. The last row, 22, is
merged to one cell, the lenght of A- AH. I would like the row to expand
based on the amount of text that is inputted into it. I have tried the
following coding but it doesn't seem to work.

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

I have placed the above coding in Module2. The document is also protected
allowing the user access to input areas only.

I don't know why it's not working. Any help would be appreciated.

Thanks!
--
Jamie