J
Joe
Is there any reason why the following code would work fine in excel 2003 but
not in 2000? The code is to get around the autofit issue with merged cells.
Thanks very much,
Joe
Dim OldRng As Range ' DECLARED THIS AT TOP OF MODULE
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
Dim Protected As Boolean
If Target.Column = 1 And (Target.Row > 16 And Target.Row < 42) Then
Cells(Target.Row, 2).Select
End If
Protected = False
Set c = Cells(Target.Row, 1)
If OldRng Is Nothing Then Set OldRng = c
If Not Intersect(OldRng, c) Is Nothing Then
Application.ScreenUpdating = True
If Me.ProtectContents Then
Protected = True
Me.Unprotect
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
ma.Locked = False
If Protected Then Me.Protect
Application.ScreenUpdating = True
End If
Set OldRng = Target
Set c = Nothing
Set OldRng = Nothing
End Sub
not in 2000? The code is to get around the autofit issue with merged cells.
Thanks very much,
Joe
Dim OldRng As Range ' DECLARED THIS AT TOP OF MODULE
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
Dim Protected As Boolean
If Target.Column = 1 And (Target.Row > 16 And Target.Row < 42) Then
Cells(Target.Row, 2).Select
End If
Protected = False
Set c = Cells(Target.Row, 1)
If OldRng Is Nothing Then Set OldRng = c
If Not Intersect(OldRng, c) Is Nothing Then
Application.ScreenUpdating = True
If Me.ProtectContents Then
Protected = True
Me.Unprotect
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
ma.Locked = False
If Protected Then Me.Protect
Application.ScreenUpdating = True
End If
Set OldRng = Target
Set c = Nothing
Set OldRng = Nothing
End Sub