How to tell when text is too long for cell dimensions?

[Jason Weiss]

Hi,

I'd like to write a macro to deal with cells that do not have enough space
to show the text they contain. Right now, Excel simply truncates the text
at the right edge of the cell if the next cell to the right has anything in
it. What I'd like to do is show the full text in a tooltip when the user
mouses over the cell. I'm stuck at the first step, though, which is knowing
when a cell needs to truncate its text.

Can anyone help?

Thanks...

....Jay

 

[Dave Peterson]

One way is to copy the cell to a new sheet. Then autofit the column width and
then compare columnwidths to see if it's bigger.

I think I'd run this on demand (maybe once???):

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim NewColWidth As Double
Dim testWks As Worksheet

Set myRng = Selection
Set testWks = Worksheets.Add

For Each myCell In myRng.Cells
With myCell
testWks.Range("a1").Clear
.Copy _
Destination:=testWks.Range("a1")
testWks.Range("a1").EntireColumn.AutoFit
NewColWidth = testWks.Columns(1).ColumnWidth
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
If NewColWidth > myCell.EntireColumn.ColumnWidth Then
.AddComment Text:=myCell.Text
End If
End With
Next myCell

Application.DisplayAlerts = False
testWks.Delete
Application.DisplayAlerts = True

End Sub