Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to go
back and click on the cell?

Thanks,
Steve

Don't use selection change. Try the change event...

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub
--
HTH...

Jim Thomlinson


"SteveFerd" wrote:

Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to go
back and click on the cell?

Thanks,
Steve

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to
use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to
go
back and click on the cell?

Thanks,
Steve

Words of warning.

First, if you are playing with VBA, you DO know enough to be dangerous <vbg

Second, if you can, avoid merged cells. They cause more problems than they
are worth. You can usually design around it.

--
HTH

Bob Phillips

"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to
use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to
go
back and click on the cell?

Thanks,
Steve

Thanks everyone for your help. I changed my worksheet event from a selection
change to a change event. Now nothing happens, even when I go back and click
on the cell after entering the text.
Also a combination of a change event and an if statement targeting column 4
didn't work.
Any other ideas?

"STEVE BELL" wrote:

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to
use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to
go
back and click on the cell?

Thanks,
Steve

Steve,

Make sure that the event macro is in the sheet module and not in a regular
module.
Here is another version (replace Macro6 with the name of your macro)
Remember that the change event is fired by entering anything into a cell and
that leaving that cell.
It does not fire if you select a cell.

To target column 4 make sure your if statement
If target.column = 4 then

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro6
End Sub

If this doesn't work
add a Msgbox line to give you a signal
add a msgbox to the other macro to see if it gets called.

Private Sub Worksheet_Change(ByVal Target As Range)
Msgbox "Event happened"
Call Macro6
End Sub

If none of this helps, than show us your code and tell us where it is
located....
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Thanks everyone for your help. I changed my worksheet event from a
selection
change to a change event. Now nothing happens, even when I go back and
click
on the cell after entering the text.
Also a combination of a change event and an if statement targeting column
4
didn't work.
Any other ideas?

"STEVE BELL" wrote:

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and
don't
really even know enough to be dangerous. I am developing a template
for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to
use
word wrap. I followed previous advice and use the following code from
Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will
require
wrap then press enter, it does not automatically wrap. But when I go
back
and click on the cell, it wraps. What do I need to do so I don't have
to
go
back and click on the cell?

Thanks,
Steve

Okay, this works.
Try this:

Make sure you keep the

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SU

That Jim and Steve told you to change.

and in your *Sub AutoFitMergedCellRowHeight () * macro right above th
first if statement add:

ACTIVECELL.OFFSET(-1, 0).SELECT[/b]

AND HERE IS WHAT YOU END UP WITH.



SUB AUTOFITMERGEDCELLROWHEIGHT()

DIM CURRENTROWHEIGHT AS SINGLE, MERGEDCELLRGWIDTH AS SINGLE
DIM CURRCELL AS RANGE
DIM ACTIVECELLWIDTH AS SINGLE, POSSNEWROWHEIGHT AS SINGLE

[b]ACTIVECELL.OFFSET(-1, 0).SELEC

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If

End Sub

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SU
---------------------------------------------------

Now after you enter the text and you press enter it will change it fo
you automatically and remain to be the cell that you entered the tex
in.

Hope this is what you were looking for

--
malik64

-----------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...fo&userid=2419
View this thread: http://www.excelforum.com/showthread.php?threadid=38855

Steve,
Thanks for your help here, I really appreciate it.
I do have the code in the sheet module, and the code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call AutoFitMergedCellRowHeight
End Sub

Nothing happens.

When I added MsgBox "event happened" the macro still doesn't fire, but
the msg box does appear as expected, so there must be an issue with the macro.

The following code from Jim Rech is included in module 1:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Is this code not compatible with a change event?

Thanks,
Steve



"STEVE BELL" wrote:

Steve,

Make sure that the event macro is in the sheet module and not in a regular
module.
Here is another version (replace Macro6 with the name of your macro)
Remember that the change event is fired by entering anything into a cell and
that leaving that cell.
It does not fire if you select a cell.

To target column 4 make sure your if statement
If target.column = 4 then

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro6
End Sub

If this doesn't work
add a Msgbox line to give you a signal
add a msgbox to the other macro to see if it gets called.

Private Sub Worksheet_Change(ByVal Target As Range)
Msgbox "Event happened"
Call Macro6
End Sub

If none of this helps, than show us your code and tell us where it is
located....
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Thanks everyone for your help. I changed my worksheet event from a
selection
change to a change event. Now nothing happens, even when I go back and
click
on the cell after entering the text.
Also a combination of a change event and an if statement targeting column
4
didn't work.
Any other ideas?

"STEVE BELL" wrote:

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and
don't
really even know enough to be dangerous. I am developing a template
for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to
use
word wrap. I followed previous advice and use the following code from
Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will
require
wrap then press enter, it does not automatically wrap. But when I go
back
and click on the cell, it wraps. What do I need to do so I don't have
to
go
back and click on the cell?

Thanks,
Steve

If the msgbox shows, than the event is firing OK.
Put a msgbox at the begining of the called macro to
see if it is being called.

If it is not being called than check the Call statement in the event macro.
Make sure the macro name is spelled correctly (I do this with copy/paste)
Try removing the word "Call".
Make sure that this macro is in a standard module.
Compile your workbook and check for any issues with any of your code.

Also look at the response from malik641...

hth

--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Steve,
Thanks for your help here, I really appreciate it.
I do have the code in the sheet module, and the code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call AutoFitMergedCellRowHeight
End Sub

Nothing happens.

When I added MsgBox "event happened" the macro still doesn't fire, but
the msg box does appear as expected, so there must be an issue with the
macro.

The following code from Jim Rech is included in module 1:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Is this code not compatible with a change event?

Thanks,
Steve



"STEVE BELL" wrote:

Steve,

Make sure that the event macro is in the sheet module and not in a
regular
module.
Here is another version (replace Macro6 with the name of your macro)
Remember that the change event is fired by entering anything into a cell
and
that leaving that cell.
It does not fire if you select a cell.

To target column 4 make sure your if statement
If target.column = 4 then

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macro6
End Sub

If this doesn't work
add a Msgbox line to give you a signal
add a msgbox to the other macro to see if it gets called.

Private Sub Worksheet_Change(ByVal Target As Range)
Msgbox "Event happened"
Call Macro6
End Sub

If none of this helps, than show us your code and tell us where it is
located....
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Thanks everyone for your help. I changed my worksheet event from a
selection
change to a change event. Now nothing happens, even when I go back and
click
on the cell after entering the text.
Also a combination of a change event and an if statement targeting
column
4
didn't work.
Any other ideas?

"STEVE BELL" wrote:

If you want it to happen when you edit a cell,
use an event macro - place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

You can make the code more selective using if statements
target.address="$A$1"
target.row = 1
target.column =1
or use the intersect function

write back if you need more help...
--
steveB

Remove "AYN" from email to respond
"SteveFerd" wrote in message
...
Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and
don't
really even know enough to be dangerous. I am developing a template
for a
group of 40 sales reps which will be used to track large accounts.
The
template requires the use of merged cells which will need to be able
to
use
word wrap. I followed previous advice and use the following code
from
Jim
Rech:
''Simulates row height autofit for a merged cell if the active
cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
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
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight,
_
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

This works fine, just need it to be automatic. So I added this to
the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will
require
wrap then press enter, it does not automatically wrap. But when I
go
back
and click on the cell, it wraps. What do I need to do so I don't
have
to
go
back and click on the cell?

Thanks,
Steve

Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/b] right before the first IF statement and
get this
"compile error: Wrong number of arguments or invalid property assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.

"malik641" wrote:


Okay, this works.
Try this:

Make sure you keep the

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SUB

That Jim and Steve told you to change.

and in your *Sub AutoFitMergedCellRowHeight () * macro right above the
first if statement add:

ACTIVECELL.OFFSET(-1, 0).SELECT[/b]

AND HERE IS WHAT YOU END UP WITH.



SUB AUTOFITMERGEDCELLROWHEIGHT()

DIM CURRENTROWHEIGHT AS SINGLE, MERGEDCELLRGWIDTH AS SINGLE
DIM CURRCELL AS RANGE
DIM ACTIVECELLWIDTH AS SINGLE, POSSNEWROWHEIGHT AS SINGLE

[b]ACTIVECELL.OFFSET(-1, 0).SELECT

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If

End Sub

PRIVATE SUB WORKSHEET_CHANGE(BYVAL TARGET AS RANGE)
AUTOFITMERGEDCELLROWHEIGHT
END SUB
---------------------------------------------------

Now after you enter the text and you press enter it will change it for
you automatically and remain to be the cell that you entered the text
in.

Hope this is what you were looking for!


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388557

Yeah you will get that if you have ".Select[/b]" in your code. make sure
it says ".Select" WITHOUT the "[/b]".

If you have that "[/b]" in your CODE, delete it and it SHOULD work
fine...at least it does for me...If it STILL happens...then please post
your code again and I'll check it out.

SteveFerd Wrote:
Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/b] right before the first IF statement
and
get this
"compile error: Wrong number of arguments or invalid property
assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.




--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388557

Thanks malik, that did it. I really appreciate the help!



"malik641" wrote:


Yeah you will get that if you have ".Select[/b]" in your code. make sure
it says ".Select" WITHOUT the "[/b]".

If you have that "[/b]" in your CODE, delete it and it SHOULD work
fine...at least it does for me...If it STILL happens...then please post
your code again and I'll check it out.

SteveFerd Wrote:
Ok, I now agree, merged cells are definitely evil. I added
ACTIVECELL.OFFSET(-1, 0).SELECT[/b] right before the first IF statement
and
get this
"compile error: Wrong number of arguments or invalid property
assignment"
the *.select* in the added code is highlighted.

Thanks everyone for your time, I appreciate it.




--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388557

Awesome! Glad to hear it's working. Thanks for the feedback.

SteveFerd Wrote:
Thanks malik, that did it. I really appreciate the help!



--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388557