What you see is what I want

[Gary''s Student]

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.

 

[p45cal]

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 i
very
wide. I want to see the portion of the sentence that actually appear
in A1.

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

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

 

[Patrick Molloy]

may we see the code yu did for the sub please?

 

[Gary''s Student]

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.

 

[Peter T]

It would be extremely difficult to do that in a UDF. The normal way is to
copy the formatted cell to a cell in an empty column and Autowidth. Another
less common but more accurate way is with an AutoSize textbox. Either way
means code to change the interface, which of course is not (normally)
possible in a UDF.

Maybe a Change event would suit your purposes.

Regards,
Peter T

 

[Patrick Molloy]

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.

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.

 

[Patrick Molloy]

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.

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]

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

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.