Thanks for the advise.
--
Gary''s Student - gsnu200905


"Patrick Molloy" wrote:

following on, I don't think that there's an excel way to get the font size
out, but I suspect that you could do this with some API call.

"Patrick Molloy" wrote:

I see the issue here - and that is of course that a function can only affect
the caller - so the line that autofits the column width failes in a function
while of course its fine in a sub

The only thing that I could think of was to calculate the width of each
letter then compare to the actual cell width.

"Gary''s Student" wrote:

Sub measure()
Dim c As Range, v As String, w As Variant
Dim shorter As String
v = Range("A1").Value
Set c = Columns("A")
w = c.Width
c.AutoFit
w2 = c.Width
If w = w2 Then
MsgBox (v)
Exit Sub
End If

For i = 1 To Len(v)
shorter = Left(v, Len(v) - i)
Range("A1").Value = shorter
c.AutoFit
w2 = c.Width
If w2 <= w Then
MsgBox (shorter)
Range("A1").Value = v
Exit Sub
End If
Next

End Sub


Basically, the sub does trial AutoFits. If you AutoFit and the column width
has not changed, then the entire text fits in the cell. If the column width
has changed, I keep chopping characters off the right until the the AutoFit
does not expand the column.

This approach has at least three problems:

1. A Sub can change the column width, a UDF cannot.
2. AutoFit responds to other cells in column.
3, The approach assumes the text is left justified.

Any help will be greatly appreciated.
--
Gary''s Student - gsnu200905


"p45cal" wrote:


Gary''s Student;506433 Wrote:
I am looking for a UDF that will return the visible text in a cell.
For
example, if A1 contains the sentence:

"Now is the time for all good men to come to the aid of their party"

The end of the sentence will "bleed" over into B1 unless column A is
very
wide. I want to see the portion of the sentence that actually appears
in A1.

I have been able to get this with a Sub, but not a Function.


--
Gary''s Student - gsnu200905

How about posting your code so that we don't have to start from
scratch?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139234