Check if text doesn't fit in cell

[Jeff Jensen]

I only want to run "Sub AddComment()" if the text in I9 doesn't fit in the
cell. How would I modify this code?:

Sub Finalize()
ActiveSheet.Unprotect
Range("B9:M9").Select
Selection.Insert Shift:=xlDown
Range("B7:M7").Select
Selection.Copy
Range("B9:M9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Locked = True
Selection.FormulaHidden = False
Range("B9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C7:M7").Select
Application.CutCopyMode = False
Selection.ClearContents
AddComment
Range("C77").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub AddComment()
Range("I9").AddComment
Range("I9").Comment.Visible = False
Range("I9").Comment.Text Text:=Range("I9").Value
With Range("I9").Comment.Shape
..ScaleWidth 3#, msoFalse, msoScaleFromTopLeft
..ScaleHeight 1#, msoFalse, msoScaleFromTopLeft
End With
End Sub

Thanks,
Jeff

 

[Gary''s Student]

Fitting depends upon font, font size, column width, etc.

The best way:

1. record the row height
2. format the cell with wrap on
3. autofit the row
4. record the row height
5. restore the cell & row

If the height changed between steps 1 & 4, then the text did not "fit"

 

[Jeff Jensen]

Thanks, but I don't understand how this tells "Sub AddComment()" to run. It
currently run it every time "Sub Finalize()" runs but I only want it to run
it the text doesn't fit in the cell. Isn't there some kind of an "If"
statement I can add to "Sub Finalize()"? Something like: If Text Don't Fit In
Cell I9 Then AddComment.

 

[Gary''s Student]

Something like:

Sub doesitfit()
Set r = Range("B9")
x1 = r.Height
r.WrapText = True
x2 = r.Height
r.WrapText = False
If x1 <> x2 Then
MsgBox (x1 & " " & x2)
Call AddComment
End If
End Sub

 

[Jeff Jensen]

Gary''s Student,
This worked perfectly.
I can't express enough how much I appreciate your help.
Thank you,
Jeff