auto expand row hieght of merged cells  
Author Message
lucenak





PostPosted: Top

Excel Programming >> auto expand row hieght of merged cells

I found a similar issue worked on by Greg for Jamie just last week, but I can
not get this to work for my situation;

I am working on an excel 2002 spread sheet that is use as a form with
protected cells, and merged cells. The 4 merged cells need to be able to
expand the height when they enter more then what fits on 2 lines. There are
more then 12 such merged cells in this spread sheet and this is the code I
was trying to use.

Modified from what Greg posted last week;
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
Dim OldRng As Range
Const Pwd As String = "monkey"

Protected = False
Set c = Cells(Active.Range) <<<< modified this line
'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

Excel470