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?

I un-merged the cell, set all the cells to unlocked, and then re-merged them. When I the modify the cell the procedure triggers and re-formats the merged cell to multi-line.

If I then view the properties of the cell on the protection tab the "locked" box has a square in it (not cleared, not checked).

When the sheet protection is put back on I can not select that cell any more (I'm assuming because of the square status in the Locked checkbox.

I un-merged the cell, set all the cells to unlocked, and then
re-merged them. When I the modify the cell the procedure triggers and
re-formats the merged cell to multi-line.

Normally, if the upper left cell of a group is not locked and that
group gets merged, then all cells in the group become not locked. They
remain not locked when the group is unmerged.

If I then view the properties of the cell on the protection tab the
"locked" box has a square in it (not cleared, not checked).

Not able to duplicate this! Perhaps your workbook is corrupt...

When the sheet protection is put back on I can not select that cell
any more (I'm assuming because of the square status in the Locked
checkbox.

FWIW
I normally 'reset' protection when a workbook with protected sheets is
opened so code can modify locked cells without having to toggle
protection off/on. This is possible by setting the parameter
'UserInterfaceOnly=True', which doesn't persist between sessions. This
combines the best of both features in that users can modify unlocked
cells and code can modify all cells.

Merged cells are typically problematic for code anyway, and so should
be avoided. Not always possible when designing forms and so we have to
use merged cell areas. Regardless, your problem seems to be unique to
the file you're working on and so I recommend rebuilding the project in
a new file!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

First crtl-click or shift-click to select just the cells you want to
be unlocked. Then goto FormatCellsProtection and clear the
"Locked" option.

Before you protect the sheet look at the settings under
ToolsProtectionProtect Sheet.

Make sure you have enabled "select unlocked cells"


Gord




On Fri, 20 Jun 2014 21:19:15 +0100, EricLCTCS
wrote:


I un-merged the cell, set all the cells to unlocked, and then re-merged
them. When I the modify the cell the procedure triggers and re-formats
the merged cell to multi-line.

If I then view the properties of the cell on the protection tab the
"locked" box has a square in it (not cleared, not checked).

When the sheet protection is put back on I can not select that cell any
more (I'm assuming because of the square status in the Locked checkbox.