Macro to insert a comment in a selection of cells + change the font size to 14

[edge37]

Good day! Can you please help me in the following? I need to have a macro where I can select one or many specific cells in a worksheet to insert a comment (like: "approved" as an example), and also, in the same macro, I would like the comment to have a font size of 14.

Thank you for your great work.

 

[DanteAmor]

Try this:

Sub add_comments()
  Dim c As Range
  
  For Each c In Selection
    'If the cell already has a comment'
    On Error Resume Next: c.AddComment: On Error GoTo 0
    c.Comment.Visible = True
    
    'text in comment
    c.Comment.Text Text:="approved"
    
    'font style
    With c.Comment.Shape.TextFrame.Characters.Font
      .Size = 14
      .Name = "Arial"
      .Bold = False
    End With
    
    'Adjust the size of the comment box
    c.Comment.Shape.TextFrame.AutoSize = True
    
    'comment box property
    c.Comment.Shape.Placement = xlMoveAndSize
  Next
End Sub

Note: I added comments in each line of the macro, if any line does not work for you, you can simply remove it from the macro.


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[edge37]

Thank you, it is almost what I need, tyhe only thing is that the comments just sits there all at once showing all the time. I would need it to work just as normal (when hovering the cell the comments show up). Check the pic. Can you help me on that? Thanks again

 

[DanteAmor]

tyhe only thing is that the comments just sits there all at once showing all the time

Change this line:

c.Comment.Visible = False

 

[edge37]

Fantastic, works great. Thank you for your help DanteAmor.