MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

merged cells formatted by text length for height

  Asked By: Reginheraht    Date: Nov 12    Category: MS Office    Views: 952

I ran into a formatting problem and created a temporary work-
around, but there has got to be a better way to do this. The steps
are as follows:

In the main sheets, a user pulls up a userform, which includes a
textbox (limited at the moment to 165 characters) where they write
comments to the employee about that particular line (data about a
specific call). Whatever he or she writes is saved in a cell on that

At the end of the month, employee reports are built from this
information, and when there is a comment about a particular line of
data, the program inserts the comment in the cells below that specific
call information.

The problem is that since it is a supervisor writing comments to
the employee about that specific call and how it might be handled
differently, who knows how long those comments will be? . . . at the
moment, I have limited the comment box to 165 characters, because that
roughly fits a combination of merged cells about a page in width and
about 2 lines in height (which is about 30 pixels high for a cell
height setting).

Is there a way to calculate the row height necessary to accomodate
the comments written by a supervisor at whatever length? . . . sort of
like an autosize on a textbox on a userform? . . . that is . . . if
what he or she writes is 300 characters in length, then the merged
cells would obviously need to be set at higher than 30 . . . while, if
they simply wrote "good job on this one" . .. then it would only be a
normal row height because it would easily fit on one line.

Any help on this will be appreciated, thanks, and I do hope all of
you are doing very well. Heheh . .. I'm in the Chicago area, so at
the moment, it's a temp of minus-4 degrees (F) so it's . .. uh . . .
rather cold. It's a wonder at this temp that I can think to write
code at all, much less good code. And, God help me if I need to
actually run it.



8 Answers Found

Answer #1    Answered By: Fairuzah Alam     Answered On: Nov 12

Every time the there is a character entered in textbox you will have to
check if it greater than the limit and if "Yes" then change the height
of the textbox. I have written a function for the same refer to it and
customize it your needs. I hope this helps.

Private Sub TextBox1_Change()
count1 = Len(TextBox1.Text)
If (count1 > 300) Then
MsgBox ("The Max length  you can enter is 299")
Exit Sub
End If
If (count1 > 200) Then
TextBox1.Height = TextBox1.Height + 20
End If
If (count1 > 100) Then
TextBox1.Height = TextBox1.Height + 20
End If
End Sub

Note : If there are more objects below the textbox then you will have to
change the position for below it so that textbox will not overlap on
other objects.

Answer #2    Answered By: Gerardo Morgan     Answered On: Nov 12

Thanks for your suggestion, but apparently I wasn't clear . . .
the row height needed isn't for the textbox--it's for merged  cells
on a spreadsheet. When the program identifies that a comment has
been recorded in the column "J" of the entry, then it inserts a row
below that on the report and moves the comment below the entry so it
can print on the same page, merging the cells  together so the
comment goes across the page.

However, I might be able to adjust what you suggested to set the
row height by using the LEN function to count the characters in the
comment cell. I'll mess around with it and see if I can.
Any other suggestions or help rewriting to fit will be

Answer #3    Answered By: Kawakib Mansour     Answered On: Nov 12

Not been following too closely but FWIW IMHO...

I don't think that's a good guide Scot because the font you are using is
probably proportional so different letters take up different amounts of

I tried something like this for another project where I wanted to fill
simulate columns in a listbox. The best I could do was put the text  into a
hissed textbox set to auto expand and then pick up the width.

It's tricky though because of all of the conversions to and from pixels
points inches cm and so on.

Answer #4    Answered By: Julia Hughes     Answered On: Nov 12

My most recent attempt seems to be working fairly well. What I am
doing now is to preset the font to Arial Bold Italic 10 and use a 90
character line cut-off, which is a couple of characters short of the
total line space, and then increase the height by 15 with every
additional 90-character-increment. I'm hoping that allowing a
couple characters leeway for each line will allow for the
proportional font.
I'll have to ask the supervisors if it's going to be enough,
but I increased the limit to 270 characters which is 3 total lines.
If they say it should be more, I can keep going, of course.
Initial tests showed it to look pretty good. The code is very
simple, and in fact a lot simpler than I expected:

Dim comlen as integer
comlen = Len(ActiveCell.Value)
Range(Cells(nxrow, 1), Cells(nxrow, 6)).Select
With Selection.Font
.Bold = True
.Italic = True
.Size = 10
End With
If comlen < 91 Then
Selection.Rows.RowHeight = 15.75
ElseIf comlen < 181 Then
Selection.Rows.RowHeight = 31.5
ElseIf comlen < 271 Then
Selection.Rows.RowHeight = 47.75
End If
'then the code goes on to merge cells,
'and add a border to set the comment off
'from the numbers

They are telling me that it's rare to have that long a comment,
so I'm hoping that the formatting  will fit for 99% of the cases.

Answer #5    Answered By: Scarlett Hughes     Answered On: Nov 12

Why not put


then you can let them write as long a story as they want.

Answer #6    Answered By: Marina Smith     Answered On: Nov 12

That sounds like a great idea, and makes sense. I'm a little
nervous about putting too much together in formulas, so I haven't
learned how to do that very well. I'll plug it in and run it and
let you know how it goes.

Answer #7    Answered By: Verner Fischer     Answered On: Nov 12

Brings to mind Talkin' New York Blues ....

Wintertime in New York town,
The wind blowin' snow around.
Walk around with nowhere to go,
Somebody could freeze right to the bone.
I froze right to the bone.
New York Times said it was the coldest winter in seventeen years;
I didn't feel so cold then.

Answer #8    Answered By: Luz Hayes     Answered On: Nov 12

Sorry can't resist...

And after a rocking, reeling, rolling ride,
I landed up on the downtown side;
Green'WH'ich Village.

Didn't find what you were looking for? Find more on merged cells formatted by text length for height Or get search suggestion and latest updates.