What you see is what I want

  • Thread starter Thread starter Gary''s Student
  • Start date Start date
G

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.
 
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.
 
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
 
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 said:
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.
Click to expand...
 
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 said:
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.
Click to expand...
 
Thanks for the advise.
--
Gary''s Student - gsnu200905


Patrick Molloy said:
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.
Click to expand...
 
Back
Top