I have a merged cell that should auto-size as users enter data. I understand
that VBA code is necessary and I have even attempted to use the following
which doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Can anyone help please?

hi
after this line....
Private Sub Worksheet_Change(ByVal Target As Range)
you have an end sub. delete it. you are ending the code before it starts.
also make sure word wrap in on. cell should expand after pressing enter.

regards
FSt1

"Teri" wrote:

I have a merged cell that should auto-size as users enter data. I understand
that VBA code is necessary and I have even attempted to use the following
which doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Can anyone help please?

Teri

The code works fine if all the rules are followed.

1. Cells are pre-set to wraptext.

2. Rows are pre-set to Autofit.

3. Code goes into the worksheet module

Where are you having trouble? Other than using of merged cells which I believe
is the very worst feature Excel Developers ever introduced.


Gord Dibben MS Excel MVP


On Thu, 31 Jan 2008 10:42:02 -0800, Teri wrote:

I have a merged cell that should auto-size as users enter data. I understand
that VBA code is necessary and I have even attempted to use the following
which doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Can anyone help please?

Good catch FSt1


Gord

On Thu, 31 Jan 2008 11:15:03 -0800, FSt1 wrote:

hi
after this line....
Private Sub Worksheet_Change(ByVal Target As Range)
you have an end sub. delete it. you are ending the code before it starts.
also make sure word wrap in on. cell should expand after pressing enter.

regards
FSt1

"Teri" wrote:

I have a merged cell that should auto-size as users enter data. I understand
that VBA code is necessary and I have even attempted to use the following
which doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Can anyone help please?

Or 4. if the code is correct<g

Missed that extra End Sub

See FSt1's post.


Gord

On Thu, 31 Jan 2008 12:29:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The code works fine if all the rules are followed.

Sorry, still couldn't get it to work. I think I'm going to use Word.

"Gord Dibben" wrote:

Or 4. if the code is correct<g

Missed that extra End Sub

See FSt1's post.


Gord

On Thu, 31 Jan 2008 12:29:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The code works fine if all the rules are followed.

That would be my second or third choice.

First choice.......DO NOT USE MERGED CELLS!!


Gord


On Fri, 1 Feb 2008 10:37:01 -0800, Teri wrote:

Sorry, still couldn't get it to work. I think I'm going to use Word.

"Gord Dibben" wrote:

Or 4. if the code is correct<g

Missed that extra End Sub

See FSt1's post.


Gord

On Thu, 31 Jan 2008 12:29:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The code works fine if all the rules are followed.

Okay, I've read the posts about auto sizing merged cells, copied the code,
and the merged cells are resizing, but...

Once I've entered data into the merged field and moved on to the next, I
can't go back and alter the previous merged field.

In case it matters, this is in a protected document. The merged cells are
unlocked. I'm doing this to force users to navigate through the form the
right way.


"Gord Dibben" wrote:

That would be my second or third choice.

First choice.......DO NOT USE MERGED CELLS!!


Gord


On Fri, 1 Feb 2008 10:37:01 -0800, Teri wrote:

Sorry, still couldn't get it to work. I think I'm going to use Word.

"Gord Dibben" wrote:

Or 4. if the code is correct<g

Missed that extra End Sub

See FSt1's post.


Gord

On Thu, 31 Jan 2008 12:29:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The code works fine if all the rules are followed.