automatic comment box size change

[mikeha_99]

Hello,
Thanks to Mr. Excel I have a functioning macro that puts a cell comment in based on the contents of certain cells. Plus, it adjusts the size of the font up to 16. This is working well, but I need one more tweak.

I would like it if the comment boxes were either larger at a preset size (height and width) or if they could automatically be sized based on the amount of text. Below is my current code:

Sub addcmt()
Dim LR As Long
LR = Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
   Range("C" & i).NoteText Text:=Range("K" & i).Value
Next i
Dim c As Range, fSize As String
fSize = 16
If fSize = "" Or Not IsNumeric(Val(fSize)) Or Val(fSize) < 1 Then
    Exit Sub
Else
    fSize = Val(fSize)
End If
On Error Resume Next
For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
    If c Is Nothing Then
        MsgBox "Can't find any cells with comments on the ActiveSheet"
        Exit Sub
    End If
    With c.Comment.Shape.TextFrame.Characters
        .Font.Size = fSize
    End With
Next c
End Sub

Any and all help is greatly appreciated.

Mike

 

[VoG]

Try

    With c.Comment.Shape.TextFrame
        .Characters.Font.Size = fSize
        .AutoSize = True
    End With

 

[mikeha_99]

As always...Thanks. This is perfect.

Mike