Sorry if this has been posted before, but have spent a fair bit of
time searching through the google pages, but didn't find quite the
right thing :)

I have a worksheet, which is about 4-5 pages long in total, that is
set to take all sorts of data entries (both numeric and text) that are
going to be anything from a 2-3 word entry up to quite a lengthy
string of words. The users entering this data will be using the "Alt +
Enter" method to simulate a carraige return. The problem I am having
is that the sheet needs to hold some form of "presentable" format when
either printed or viewed on screen and as such have had to set the row
heights to a uniform measurement to ensure a presence (albeit a rather
dubious one)of professionalism on the users behalf.

When all of their entries are fairly small (perhaps there are only two
or three lines of data in a cell) there is no problem as the end
result looks good (all cells aligned , centred, e.t.c.)but as soon as
they go "over the limit" of the set row height, the rest of the text
is then hiden under the cell border of the next cell below it.

Is there any way that I can use a macro that will automatically
correct the row height (perhaps as soon as the user hits "enter" to go
to the next cell), so that those cells, and those cells only, that due
to the quantity of text entered need to exceed the pre-set height (say
size 30 for example), will automatically be adjusted to a row height
that then shows all data entered ?

Due to the nature of the sheet itself and the numeric data in other
cells higher up the sheet, I need this to happen to only a specific
"range" of rows (for example rows 95 through to 150 inclusive).

Would like to express my most sincerest thanks in advance for any help
that you may be able to give with this headache.


Best regards,
Roy.

Roy,

You could use the WorkSheetChange event to accomplish this. Right-click on
the sheet(s) and choose "View Code". Then paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Rows("95:150")) Is Nothing Then
Application.ScreenUpdating = False
Target.Rows.AutoFit
If Target.RowHeight < 30 Then Target.RowHeight = 30 'keeps the height at
least 30
Application.ScreenUpdating = True
End If

End Sub

hth,

Doug

"roy" wrote in message
m...
Sorry if this has been posted before, but have spent a fair bit of
time searching through the google pages, but didn't find quite the
right thing :)

I have a worksheet, which is about 4-5 pages long in total, that is
set to take all sorts of data entries (both numeric and text) that are
going to be anything from a 2-3 word entry up to quite a lengthy
string of words. The users entering this data will be using the "Alt +
Enter" method to simulate a carraige return. The problem I am having
is that the sheet needs to hold some form of "presentable" format when
either printed or viewed on screen and as such have had to set the row
heights to a uniform measurement to ensure a presence (albeit a rather
dubious one)of professionalism on the users behalf.

When all of their entries are fairly small (perhaps there are only two
or three lines of data in a cell) there is no problem as the end
result looks good (all cells aligned , centred, e.t.c.)but as soon as
they go "over the limit" of the set row height, the rest of the text
is then hiden under the cell border of the next cell below it.

Is there any way that I can use a macro that will automatically
correct the row height (perhaps as soon as the user hits "enter" to go
to the next cell), so that those cells, and those cells only, that due
to the quantity of text entered need to exceed the pre-set height (say
size 30 for example), will automatically be adjusted to a row height
that then shows all data entered ?

Due to the nature of the sheet itself and the numeric data in other
cells higher up the sheet, I need this to happen to only a specific
"range" of rows (for example rows 95 through to 150 inclusive).

Would like to express my most sincerest thanks in advance for any help
that you may be able to give with this headache.


Best regards,
Roy.

Nope, didn't work i'm afraid.

It did change any row height less than 30 within the target range to
the height of 30 after hitting the enter button, but if the contents
of a cell in a row caused that cell to be "too small" in height(i.e.
needs a height of something like 42 or 51 or 65 for example), it
doesn't expand the cell to a height which allows the actual data in
the cell to be seen.

Any ideas on how it can do this ?

Regards,
Roy.


"Doug Glancy" wrote in message ...

You could use the WorkSheetChange event to accomplish this. Right-click on
the sheet(s) and choose "View Code". Then paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Rows("95:150")) Is Nothing Then
Application.ScreenUpdating = False
Target.Rows.AutoFit
If Target.RowHeight < 30 Then Target.RowHeight = 30 'keeps the height at
least 30
Application.ScreenUpdating = True
End If

End Sub

hth,

Doug

"roy" wrote in message
m...
Sorry if this has

You're right. I had not noticed that in XL2000, at least, the rows are
autofit by default as you describe, although I could swear that sometimes
they are not. Anyways, it makes it hard for me to test, as it's already
happening by default. I did look at archived Google Groups and there seemed
to be issues with XL 97 and merged cells. Would that apply?

Sorry to not be more help.

Doug

"roy" wrote in message
m...
Nope, didn't work i'm afraid.

It did change any row height less than 30 within the target range to
the height of 30 after hitting the enter button, but if the contents
of a cell in a row caused that cell to be "too small" in height(i.e.
needs a height of something like 42 or 51 or 65 for example), it
doesn't expand the cell to a height which allows the actual data in
the cell to be seen.

Any ideas on how it can do this ?

Regards,
Roy.


"Doug Glancy" wrote in message
...

You could use the WorkSheetChange event to accomplish this. Right-click
on
the sheet(s) and choose "View Code". Then paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Rows("95:150")) Is Nothing Then
Application.ScreenUpdating = False
Target.Rows.AutoFit
If Target.RowHeight < 30 Then Target.RowHeight = 30 'keeps the
height at
least 30
Application.ScreenUpdating = True
End If

End Sub

hth,

Doug

"roy" wrote in message
m...
Sorry if this has

Yes, the version I am using is XL97 and the rows are merged across
anywhere between 2 and 6 columns. Many thanks for your assistance
Doug, perhaps there's some kind soul out there who can look at this in
XL97 and see if there's a way around it ?

Regards,
Roy.

"Doug Glancy" wrote in message ...
You're right. I had not noticed that in XL2000, at least, the rows are
autofit by default as you describe, although I could swear that sometimes
they are not. Anyways, it makes it hard for me to test, as it's already
happening by default. I did look at archived Google Groups and there seemed
to be issues with XL 97 and merged cells. Would that apply?

Sorry to not be more help.

Doug

"roy" wrote in message
m...
Nope, didn't work i'm afraid.

It did change any row height less than 30 within the target range to
the height of 30 after hitting the enter button, but if the contents
of a cell in a row caused that cell to be "too small" in height(i.e.
needs a height of something like 42 or 51 or 65 for example), it
doesn't expand the cell to a height which allows the actual data in
the cell to be seen.

Any ideas on how it can do this ?

Regards,
Roy.


"Doug Glancy" wrote in message
...

You could use the WorkSheetChange event to accomplish this. Right-click
on
the sheet(s) and choose "View Code". Then paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Rows("95:150")) Is Nothing Then
Application.ScreenUpdating = False
Target.Rows.AutoFit
If Target.RowHeight < 30 Then Target.RowHeight = 30 'keeps the
height at
least 30
Application.ScreenUpdating = True
End If

End Sub

hth,

Doug

"roy" wrote in message
m...
Sorry if this has

Roy,

This is a long shot (and I am not sure how to get there from here)... But
working in Exxcel 97 the autofit doesn't work on a cell with merged rows or
merged columns.

Could you somehow restrict the entry to a single cell and than do the merge?
Than you could autofit before the merge. That works.

Or find a way to count the number of carriage returns in the text and set
the
height accordingly?

Below are the code equivalents... Not sure which one to use.

vbCrLf or Chr(13) + Chr(10) Carriage return-linefeed combination
vbCr or Chr(13) Carriage return character
vbLf or Chr(10) Linefeed character
vbNewLine or Chr(13) + Chr(10) or Chr(13) Platform-specific new line
character; whichever is appropriate for current platform

--
sb
"roy" wrote in message
om...
Yes, the version I am using is XL97 and the rows are merged across
anywhere between 2 and 6 columns. Many thanks for your assistance
Doug, perhaps there's some kind soul out there who can look at this in
XL97 and see if there's a way around it ?

Regards,
Roy.

"Doug Glancy" wrote in message
...
You're right. I had not noticed that in XL2000, at least, the rows are
autofit by default as you describe, although I could swear that
sometimes
they are not. Anyways, it makes it hard for me to test, as it's already
happening by default. I did look at archived Google Groups and there
seemed
to be issues with XL 97 and merged cells. Would that apply?

Sorry to not be more help.

Doug

"roy" wrote in message
m...
Nope, didn't work i'm afraid.

It did change any row height less than 30 within the target range to
the height of 30 after hitting the enter button, but if the contents
of a cell in a row caused that cell to be "too small" in height(i.e.
needs a height of something like 42 or 51 or 65 for example), it
doesn't expand the cell to a height which allows the actual data in
the cell to be seen.

Any ideas on how it can do this ?

Regards,
Roy.


"Doug Glancy" wrote in message
...

You could use the WorkSheetChange event to accomplish this.
Right-click
on
the sheet(s) and choose "View Code". Then paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Rows("95:150")) Is Nothing Then
Application.ScreenUpdating = False
Target.Rows.AutoFit
If Target.RowHeight < 30 Then Target.RowHeight = 30 'keeps the
height at
least 30
Application.ScreenUpdating = True
End If

End Sub

hth,

Doug

"roy" wrote in message
m...
Sorry if this has