I am trying to paste in the code to ensure the merged cells in my spreadsheet
autofit the text that is written.
I have followed previous queries on the subject, and used the code provided.
Never having attempted this before, of course it isn't working and I don't
know why.
Any help would be appreciated

Hi LDF,

See David McRitchie's 'Getting Started With Macros And User Defined
Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm



---
Regards,
Norman


"LDF" wrote in message
...
I am trying to paste in the code to ensure the merged cells in my
spreadsheet
autofit the text that is written.
I have followed previous queries on the subject, and used the code
provided.
Never having attempted this before, of course it isn't working and I don't
know why.
Any help would be appreciated

Perhaps this isn't your problem, but just to be su
Assuming you are using Jim Rech's code, you have to run the code for it to
do anything. Just putting it in a module does not cause it to autofit
merged cells.

Also, this line
If .Rows.Count = 1 And .WrapText = True Then
tells you that it only works for single row, multiple column merged cells
(with WrapText set to true)

--
regards,
Tom Ogilvy



"LDF" wrote in message
...
I am trying to paste in the code to ensure the merged cells in my
spreadsheet
autofit the text that is written.
I have followed previous queries on the subject, and used the code
provided.
Never having attempted this before, of course it isn't working and I don't
know why.
Any help would be appreciated

Is there any reason why this code would work fine in Excel 2003 but not in
Excel 2000?

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

"Tom Ogilvy" wrote:

Perhaps this isn't your problem, but just to be su
Assuming you are using Jim Rech's code, you have to run the code for it to
do anything. Just putting it in a module does not cause it to autofit
merged cells.

Also, this line
If .Rows.Count = 1 And .WrapText = True Then
tells you that it only works for single row, multiple column merged cells
(with WrapText set to true)

--
regards,
Tom Ogilvy



"LDF" wrote in message
...
I am trying to paste in the code to ensure the merged cells in my
spreadsheet
autofit the text that is written.
I have followed previous queries on the subject, and used the code
provided.
Never having attempted this before, of course it isn't working and I don't
know why.
Any help would be appreciated

It was originally written in the xl97/xl2000 timeframe, so I don't see any
reason it would not work in all versions after xl95 (merged cells were
introduced in xl97).

--
Regards,
Tom Ogilvy


"Joe" wrote in message
...
Is there any reason why this code would work fine in Excel 2003 but not in
Excel 2000?

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

"Tom Ogilvy" wrote:

Perhaps this isn't your problem, but just to be su
Assuming you are using Jim Rech's code, you have to run the code for it
to
do anything. Just putting it in a module does not cause it to autofit
merged cells.

Also, this line
If .Rows.Count = 1 And .WrapText = True Then
tells you that it only works for single row, multiple column merged
cells
(with WrapText set to true)

--
regards,
Tom Ogilvy



"LDF" wrote in message
...
I am trying to paste in the code to ensure the merged cells in my
spreadsheet
autofit the text that is written.
I have followed previous queries on the subject, and used the code
provided.
Never having attempted this before, of course it isn't working and I
don't
know why.
Any help would be appreciated