Forum Discussion

Ko Lay

Copper Contributor

Sep 05, 2018

Solved

Excel default comment setting

Hi, I couldn't find how to change the default setting for comment in excel (Office 2016). Please help. Thank you.

excel

JKPieterse

Sep 07, 2018

This example macro formats a comment as a diamond shape with a red border:

Sub AddAndFormatComment()
    With ActiveCell
        If .Comment Is Nothing Then
            .AddComment
            .Comment.Text Text:=Application.UserName & Chr(10) & ""
        End If
        With .Comment.Shape
            .AutoShapeType = msoShapeDiamond
            .Line.ForeColor.RGB = vbRed
        End With
        With .Font
            .Name = "Tahoma"
            .Size = 10
        End With
    End With
End Sub

JKPieterse

Silver Contributor

Sep 06, 2018

As far as I know there is no built-in way to do that. Instead you could perhaps record a macro which inserts a comment and adjusts your comment style and then run that macro instead of adding the comment using shift+F2?

Ko Lay

Copper Contributor

Sep 06, 2018

I'm not sure how to do that but I'll give it a try.

Thank you.

JKPieterse

Silver Contributor

Sep 07, 2018

This example macro formats a comment as a diamond shape with a red border:

Sub AddAndFormatComment()
    With ActiveCell
        If .Comment Is Nothing Then
            .AddComment
            .Comment.Text Text:=Application.UserName & Chr(10) & ""
        End If
        With .Comment.Shape
            .AutoShapeType = msoShapeDiamond
            .Line.ForeColor.RGB = vbRed
        End With
        With .Font
            .Name = "Tahoma"
            .Size = 10
        End With
    End With
End Sub

Ko Lay
Copper Contributor
Sep 07, 2018
Hi Jan,
Thank you very much.