COMMENT (MS EXCEL 2003)

Topic

New Reply

When using the comment it always comes up in FONT 8 can I change the default on that to 10?

Reply | Quote

Replies

Only by changing the Windows display settings for the ToolTip. You can however use code to insert a comment with any formatting you like. For example:

Sub AddMyComment()
    Dim rngCell As Range
    For Each rngCell In Selection
        With rngCell
            .AddComment Application.UserName & ": " & vbLf
            With .Comment
                With .Shape
                    With .TextFrame
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                        .ReadingOrder = xlContext
                        .AutoSize = False
                        With .Characters.Font
                           .Name = "Arial"
                           .Size = 10
                        End With
                    End With ' .TextFrame
                    With .Fill
                        .Visible = msoTrue
                        .Solid
                        .ForeColor.SchemeColor = 47
                        .Transparency = 0#
                    End With ' .Fill
                    With .Line
                        .Weight = 0.75
                        .DashStyle = msoLineSolid
                        .Style = msoLineSingle
                        .Transparency = 0#
                        .Visible = msoTrue
                        .ForeColor.SchemeColor = 53
                        .BackColor.RGB = RGB(255, 255, 255)
                    End With ' .Line
                    .AutoShapeType = msoShapeRoundedRectangle
                End With ' .Shape
                .Visible = True
            End With ' .Comment
        End With ' rngCell
    Next rngCell
End Sub

Reply | Quote

Thank you, that is amazing. I tried the comment format and it showed it was on 10 but when I start a comment it is still on 8 ?

Reply | Quote

You have to use that code every time you create a comment – it does not change the default comment style. You can assign it to the standard ‘Insert->Comment’ menu item if you wish.

Reply | Quote

Hi R J B P

There are some good tips on comments on the Contextures web site http://www.contextures.com/xlcomments02.html%5B/url%5D , hope these are useful

Cheers

Steve

Reply | Quote

Thank you Steve for the information, Richard the long name was changed when I went in the military to R.J.B. Phillips III, there is now a Richard John Blackmoor Phillips V thanks again

Reply | Quote

Thanks Rory, as usual, logging into Woody’s lounge throws up something useful!!

2 questions though…..

How do you adjust the starting size of the comment?
Can the ‘comment added date’ be auto filled? alongside application.username.

Also, whilst I think of it, where can I find the ‘numeric’ vba colour pallette??

Thanks

Nathan

Reply | Quote

Excel uses a customizable palette of 56 colors for the font and fill color of cells, and for the line and fill color of charts. See Sample Visual Basic code to create a color index table in Excel.

The color of comments, can be any of the 16777216 colors supported by Windows.

Reply | Quote

To set the size of the comment, set the Width and Height of its Shape (in points, where 72 points = 1 inch)

To add the current date/time to the username, change the line

.AddComment Application.UserName & ": " & vbLf

to (for example)

.AddComment Application.UserName & " (" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & "): " & vbLf

You can, of course, use a different format.

Reply | Quote

don’t you mean “hh:mm?

Steve

Reply | Quote

Both will work. You can use n or nn for minutes to avoid confusion with m or mm for month, but adjacent to a colon : m and mm will be interpreted as minutes.

Reply | Quote

That must be new(er). I tested it in XL 97 and it put the literal “nn” in the time. I did not test in XL2002 (the version I have on another computer)

Steve

Reply | Quote

Thanks Rory.

Reply | Quote

Good one!

Reply | Quote

Could’nt resist……

🙂

Reply | Quote

Nathan, I like the questions and I could sure learn something from you people

Reply | Quote