Solved: Auto text-ellipse macro. Is it possible?

[hunsnowboard]

Hi Everyone!
Just wondering if this can be done?
I have a column in my excel file where there is text in each record. The text in most of the records overlaps the width of the cell. I was just wondering if it is possible to write a macro which auto ellipses the overlapping texts in the relevant cells.

I mean something like this:

John Doe Limited Company <- full text in the cell
l John Doe Limitl <- the text with the width of the cell without auto ellipse
l John Doe Li...l <- the text with the width of the cell with auto ellipse

Of course I wouldn't like text to be deleted, just to show ellipse when close to the border of the cell.

Is it possible to have such a macro?

Thank you in advance!

[mdmackillop]

Something like this, but you'll need to play with the fractions to suit your font size and column widths.


Private Sub Worksheet_Change(ByVal Target As Range)
x = Len(Target)
y = Target.Width
If x / y > 0.15 Then
Target = Left(Target, Int(y * 0.22) - 3) & "..."
End If
End Sub

[hunsnowboard]

Hello mdmackillop!

Thank you for the solution, it works, but my problem is that the part of the text which comes after the ellpis is deleted. I would like to still have that part and be able to see it in the formula bar. The macro you wrote deletes the overflowing part of text.

I hope you understand my problem!

[mdmackillop]

I don't see how you can have a different constant value in a cell from that which appears in the formula bar. You could adjust the code to copy the full text into an adjoining hidden column and access that by double clicking the cell. All a bit messy though.

[mikerickson]

This modification of mdmackillop's code might work.
Although in my testing the elipsis stuck out past the end of the cell. The .22 multiplier might be dependent on font name and size.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long, y As Long
x = Len(Target)
y = Target.Width
If x / y > 0.15 Then
Target.NumberFormat = ";;;" & Chr(34) & Left(Target, Int(y * 0.22) - 3) & "..." & Chr(34)
End If
End Sub

[hunsnowboard]

Thank you so much mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706)!

This is exactly what I wanted! :) It works perfectly!

[mdmackillop]

Ingenious! :clap2: :clap2: :clap2: