format comment font

[mikeha_99]

Greetings,
My goal is to format the font size in comment boxes, when using a macro I found here on Mr. Excel

The macro, works great, and has been adapted to my project. The code is:

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
End Sub

I would prefer if the comment would be displayed with font size 14. Can this special format be obtained by adding some code to my existing Macro to automatically set the font size?

Mike

 

[JoeMo]

Here's some code you can adapt to your macro:

Sub ChangeCommentFontSize()
'Change size of font in all comments on activesheet
Dim c As Range, fSize As String
fSize = InputBox("Enter Font Size")
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

 

[mikeha_99]

OK, thanks! That helps a lot, but raises one more issue.

Can the comment box automatically re-size to fit all the text?

The comment boxes are fixed in size, and some text is not showing.

Does this require a new thread?


Mike

 

[JoeMo]

try experimenting with the sizing in this code:

Sub AutoSizeComments()
'Autosizes all comments on activesheet
 Dim oCom As Comment
  Dim lArea As Long
  For Each oCom In ActiveSheet.Comments
    With oCom
      .Shape.TextFrame.AutoSize = True
      If .Shape.Width > 300 Then
        lArea = .Shape.Width * .Shape.Height
        .Shape.Width = 200
        ' An adjustment factor of 1.1 seems to work ok.
        .Shape.Height = (lArea / 200) * 1.1
      End If
    End With
  Next oCom

End Sub