I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell. I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007

Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell. I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007

Gord,

Thank you for your help! There's only one thing...I have attached two codes,
the one you gave me and I have also used a code which is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when I type something in my cells, I get this "Microsoft Visual Basic
Compile error: Ambiguous name detected: Worksheet_Change. These code work
individually but not together...any suggestions?


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that
route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell.
I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007

You cannot have two events of the same type in a worksheet module.

Where are the merged cells?

Perhaps you could define a target range for the autofit code to run on that
is separate from the column 7 target range.

Then you could combine into one change event.

OR............get rid of those crappy problem-causing merged cells.

Can you use center across selection instead?


Gord


On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"
wrote:

Gord,

Thank you for your help! There's only one thing...I have attached two codes,
the one you gave me and I have also used a code which is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when I type something in my cells, I get this "Microsoft Visual Basic
Compile error: Ambiguous name detected: Worksheet_Change. These code work
individually but not together...any suggestions?


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that
route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell.
I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007

Hi Gord,
I am working with Selah on this worksheet. What I did was removed the
merged cells as you suggested. I then entered in some text in Row 44 Column
B, highlighed the cells that I wanted this text to be in (column B through G)
r-clicked selected format cells, Alignment, selected center across selection.
It worked perfectly. Thanks.

However, I am not sure how many rows a user will be inputting (could only be
Row 44 or it could be from 44-100) is there a way to incorporate some
additional code to achieve this formating as well as the code that is statted
below? I would also need the NOW date to show up in Column H for this as
well.

Does this make sense and/or is it possible?

Penny

"Gord Dibben" wrote:

You cannot have two events of the same type in a worksheet module.

Where are the merged cells?

Perhaps you could define a target range for the autofit code to run on that
is separate from the column 7 target range.

Then you could combine into one change event.

OR............get rid of those crappy problem-causing merged cells.

Can you use center across selection instead?


Gord


On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"
wrote:

Gord,

Thank you for your help! There's only one thing...I have attached two codes,
the one you gave me and I have also used a code which is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when I type something in my cells, I get this "Microsoft Visual Basic
Compile error: Ambiguous name detected: Worksheet_Change. These code work
individually but not together...any suggestions?


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that
route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell.
I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007









-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

Maybe this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range(Target, Cells(Target.Row, "G"))
If Target.Column = 2 Then
Application.EnableEvents = False
With Target
If .Value < "" Then
.Offset(, 6).Value = Now
Else
.Offset(, 6).Value = ""
End If
End With
Application.EnableEvents = True
rng.HorizontalAlignment = xlCenterAcrossSelection
End If
End Sub


Gord

On Mon, 29 Jun 2009 16:24:01 -0700, PennyM
wrote:

Hi Gord,
I am working with Selah on this worksheet. What I did was removed the
merged cells as you suggested. I then entered in some text in Row 44 Column
B, highlighed the cells that I wanted this text to be in (column B through G)
r-clicked selected format cells, Alignment, selected center across selection.
It worked perfectly. Thanks.

However, I am not sure how many rows a user will be inputting (could only be
Row 44 or it could be from 44-100) is there a way to incorporate some
additional code to achieve this formating as well as the code that is statted
below? I would also need the NOW date to show up in Column H for this as
well.

Does this make sense and/or is it possible?

Penny

"Gord Dibben" wrote:

You cannot have two events of the same type in a worksheet module.

Where are the merged cells?

Perhaps you could define a target range for the autofit code to run on that
is separate from the column 7 target range.

Then you could combine into one change event.

OR............get rid of those crappy problem-causing merged cells.

Can you use center across selection instead?


Gord


On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"
wrote:

Gord,

Thank you for your help! There's only one thing...I have attached two codes,
the one you gave me and I have also used a code which is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when I type something in my cells, I get this "Microsoft Visual Basic
Compile error: Ambiguous name detected: Worksheet_Change. These code work
individually but not together...any suggestions?


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that
route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell.
I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007

That worked perfect, thanks.

Now that I have gotten that working, how do I make it so once somebody has
entered something in those cells they can't delete or edit it once the hit
the tab/enter key?

"Gord Dibben" wrote:

Maybe this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range(Target, Cells(Target.Row, "G"))
If Target.Column = 2 Then
Application.EnableEvents = False
With Target
If .Value < "" Then
.Offset(, 6).Value = Now
Else
.Offset(, 6).Value = ""
End If
End With
Application.EnableEvents = True
rng.HorizontalAlignment = xlCenterAcrossSelection
End If
End Sub


Gord

On Mon, 29 Jun 2009 16:24:01 -0700, PennyM
wrote:

Hi Gord,
I am working with Selah on this worksheet. What I did was removed the
merged cells as you suggested. I then entered in some text in Row 44 Column
B, highlighed the cells that I wanted this text to be in (column B through G)
r-clicked selected format cells, Alignment, selected center across selection.
It worked perfectly. Thanks.

However, I am not sure how many rows a user will be inputting (could only be
Row 44 or it could be from 44-100) is there a way to incorporate some
additional code to achieve this formating as well as the code that is statted
below? I would also need the NOW date to show up in Column H for this as
well.

Does this make sense and/or is it possible?

Penny

"Gord Dibben" wrote:

You cannot have two events of the same type in a worksheet module.

Where are the merged cells?

Perhaps you could define a target range for the autofit code to run on that
is separate from the column 7 target range.

Then you could combine into one change event.

OR............get rid of those crappy problem-causing merged cells.

Can you use center across selection instead?


Gord


On Thu, 25 Jun 2009 13:33:53 -0700, "Selah West"
wrote:

Gord,

Thank you for your help! There's only one thing...I have attached two codes,
the one you gave me and I have also used a code which is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when I type something in my cells, I get this "Microsoft Visual Basic
Compile error: Ambiguous name detected: Worksheet_Change. These code work
individually but not together...any suggestions?


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Row Autofit will not work with merged cells unless you add event code to
your worksheet.

If you "MUST" use merged cells and you want to go that
route...............

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which is all one line.

http://groups.google.com/group/micro...1c160cbeb27874


Gord Dibben MS Excel MVP

On Thu, 25 Jun 2009 10:13:11 -0700, "Selah West"
wrote:

I have merged cells, so it disables Autofit. I would like to know if there
is a way to use atuofit and have merged cells.

"JLGWhiz" wrote in message
...
Maybe on the menu bar click FormatRowAutofit


"Selah West" wrote in message
...
I have a worksheet that has a comments column that is formatted to wrap
text if needed for each cell. My problem is I need the row to
automatically adjust to the height of what ever is entered in that cell.
I
want to be able to use merged cells. Is there a VBA event code to make
this happen?

Using Excel 2007