Cannot set FontName and/or FontSize in an Excel Comment with Visual Basic for Applications

Hi @John Wismeijer ,

Thanks for reaching out.

The code samples below are provided as references to show the supported approach and a suitable workaround, so they may need to be adjusted to fit your workbook structure and the rest of your VBA project.

This behavior is caused by a limitation in Excel for Mac rather than by the comment text itself. On Mac, VBA can create the comment and set its text and size, but accessing .TextFrame.Characters.Font for a comment can raise run-time error 1004, so that font formatting path is not reliably supported there.

One small correction in the code is that FontStyle does not set the font family. If you want to set the font name, the correct property is .Name, for example:

With Range("C4").Comment.Shape.TextFrame.Characters.Font
  .Name = "Tahoma"
  .Size = 10
End With

Even with that correction, Excel for Mac may still fail at .TextFrame, so it does not fully resolve the issue on Mac.

If your goal is to keep using a standard Excel comment, the safest approach on Mac is to create the comment, set the text, and resize the box only, for example:

Option Explicit

Public Sub SetCommentFontSize()
  With ActiveSheet.Range("C4")
    On Error Resume Next
    .ClearComments
    On Error GoTo 0

    .AddComment
    With .Comment
      .Visible = False
      .Text "This is a Comment"
      .Shape.Height = 30
      .Shape.Width = 200
    End With
  End With
End Sub

If you need full control over font name and size from VBA on Mac, the more reliable workaround is to use a text box shape instead of a comment, because text boxes support font formatting more consistently:

Option Explicit

Public Sub AddTextboxInsteadOfComment()
  Dim ws As Worksheet
  Dim c As Range
  Dim shp As Shape

  Set ws = ActiveSheet
  Set c = ws.Range("C4")

  Set shp = ws.Shapes.AddTextbox( _
    Orientation:=msoTextOrientationHorizontal, _
    Left:=c.Left, _
    Top:=c.Top + c.Height, _
    Width:=200, _
    Height:=30)

  With shp.TextFrame2.TextRange
    .Text = "This is a Comment"
    .Font.Name = "Tahoma"
    .Font.Size = 10
  End With
End Sub

For reference, Microsoft’s VBA documentation confirms the object model and the correct properties involved here:

• Range.Comment property
• Comment.Shape property
• Shape.TextFrame property
• Font.Name property
• Font.Size property

These pages support the object path and confirm that .Name and .Size are the proper font properties. I do not see a Microsoft VBA reference page that explicitly documents this as a Mac-specific limitation for comment formatting, so that part is based on the run-time behavior in Excel for Mac rather than on a separate platform-specific VBA article.

Hope this helps! If my explanation and the information I provided were helpful, I would greatly appreciate it if you could follow the instructions here so others with the same problem can benefit as well.