I don't know what "square symbol" you are referring to.

You can run the code on a protected sheet.

Amended code . . . . . . .

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

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

ActiveSheet.Protect Password:="justme"

Application.ScreenUpdating = True

End If
End With
End Sub


Gord


On Fri, 20 Jun 2014 14:20:52 +0100, EricLCTCS
wrote:


That handles the resizing perfectly... Thank you. However, I've run into
another issue you might have some insight on. Once this event fires it
appears to change the cell lock status from "unchecked" to a "square" (I
have no idea what this represents) and now can't change that field
anymore.

Since I don't want users to be messing with the spreadsheet format I
have the sheet protected with only unlocked cells being available for
edit.

Once one of the editable cells fires this event it's locked status is
being changed.

Do you know what a square symbol means in this indicator? Any idea why
it's changing from unchecked to the square? How might I get around
this?

Thanks for all your help.

Eric
-------------------------------------------------------------------------

'Gord Dibben[_2_ Wrote:
;1617903']This Greg Wilson worksheet event code does the trick.

Just make sure merged cells are pre-formatted to wrap text.

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




On Thu, 19 Jun 2014 17:23:29 +0100, EricLCTCS
wrote:
-

Newbie non developer here...

I have created a procedure "AutoFitMergedCellRowHeight()" in Module1
that I can manually run against a field in a spreadsheet that that
successfully resizes a cell height depending on the amount of text
entered:

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range, RangeWidth As Single

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
RangeWidth = .Width

For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
MergeCells = False
Cells(1).ColumnWidth = MergedCellRgWidth
While .Cells(1).Width < RangeWidth
Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
Wend
Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5

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 also created a procedure under Sheet4:

Sub Worksheet_Change(ByVal Target As Range)

'MsgBox "You just changed " & Target.Address
Call Module1.AutoFitMergedCellRowHeight

End Sub

I effective want a cell to automatically resize itself after the cell
value has changed.

The manually triggered resize works fine but the auto resize does not.

Any insight would be appreciated.

EricLCTCS-