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

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

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

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-

Let me try to rephrase... When the re-size procedure is triggered it appears to be turning on the locked indicator in one of the cells that make up the total merged cell (maybe that's why it's displaying a square instead of the check on the lock indicator.

As I understand it, in order for user to be limited to enter data into only certain cells you set the locked indicator to on for those cells and set it to off of the cells you want editable. When protecting the sheet I am only allowing modification to unlocked cells.

What appears to be happening when the procedure triggers is that it re-sizes the merged cells and then turns on the lock indicator in one of the cells that make up the merged cell, thus locking the entire merged cell to further modification once the procedure re-enabled the sheet protection.

Does that make sense?

Maybe the better question is what code needs to be added to your last code to explicitly unlock the cell range you just re-sized?

Wouldn't take clear the issue up?

Not sure what's going on. I cannot replicate.

Do you set unlocked property on idividual cells then merge them into
one?

Does it make a difference if you set the unlocked property on the
merged cells post merging?

Gord

On Fri, 20 Jun 2014 17:31:36 +0100, EricLCTCS
wrote:


Let me try to rephrase... When the re-size procedure is triggered it
appears to be turning on the locked indicator in one of the cells that
make up the total merged cell (maybe that's why it's displaying a square
instead of the check on the lock indicator.

As I understand it, in order for user to be limited to enter data into
only certain cells you set the locked indicator to on for those cells
and set it to off of the cells you want editable. When protecting the
sheet I am only allowing modification to unlocked cells.

What appears to be happening when the procedure triggers is that it
re-sizes the merged cells and then turns on the lock indicator in one of
the cells that make up the merged cell, thus locking the entire merged
cell to further modification once the procedure re-enabled the sheet
protection.

Does that make sense?